Définition de commandes en Français et Anglais

**Pierre Fauconnier** · 28/07/2020, 11h14

Le problème de Objet Requis vient du fait que lorsque tu déplaces d'une ligne par drag and drop, TestRange est remplacé par la cellule que tu déplaces, et le VBA en perd la trace. C'est toujours un objet Range, mais VBA ne sait plus l'utiliser. A cela s'ajoute le fait que lors d'un déplacement, il y a deux "Change" coup sur coup, celui de la ligne qui est déplacée puis celui de la ligne sur laquelle tu atterris. Il faut donc désactiver le EnableEvents et passer par une gestion d'erreur pour le remettre.

Pour pallier ce problème et éviter la suppression en cas de déplacement par drag and drop, je propose donc un test sur le numéro de la ligne d'arrivée et une capture de l'erreur au travers du code suivant:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Private Sub Worksheet_Change(ByVal Target As Range)
  On Error GoTo Catch
  If Not TestRange Is Nothing And Target.Row + 1 <> TestRow Then
    Application.EnableEvents = False
    If Target.Rows.Count = 1 And Target.Count = 16384 Then
      If TestRow > TestRange.Row Or (TestRow = TestRange.Row And Target(8) = "") Then
        If SheetExists(Value) Then MsgBox "Suppression" 'Worksheets(Value).Delete
      End If
    End If
  End If
 
Catch:
  Application.EnableEvents = True
End Sub

Dans ce cas, je ne vois pas comment éviter la gestion d'erreur avec un On Error car, à ma connaissance, il n'y a pas moyen de tester par code le problème de Objet requis.

**pijaku** · 28/07/2020, 11h43

Bonjour Pierre, Bonjour tout le monde,

Envoyé par Pierre Fauconnier

Dans ce cas, je ne vois pas comment éviter la gestion d'erreur avec un On Error car, à ma connaissance, il n'y a pas moyen de tester par code le problème de Objet requis.

La fonction VarType renvoie, si un objet est passé ET qu’il possède une propriété par défaut, le type de la propriété par défaut de l’objet.
Dans ce cas, l'objet passé (Range), possède (ou non) une propriété par défaut, selon s'il est "requis" ou non.

Si l'objet est requis, VarType va renvoyer 9 (Objet), sinon VarType va renvoyer le type de la propriété par défaut, chez moi 5.

Il suffit donc, peut-être, de tester comme ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
Option Explicit
 
Private Value As String
Private TestRange As Range
Private TestRow As Long
 
Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Rows.Count = 1 And Target.Count = 16384 Then
    If VarType(TestRange) <> 9 Then                                            ' *-*-*-*-*-*-*-*-*-*-*-* TEST Objet requis
        If TestRow > TestRange.Row Or (TestRow = TestRange.Row And Target(8) = "") Then
            If SheetExists(Value) Then MsgBox "Worksheets(Value).Delete"
        End If
    Else
 
    End If
  End If
End Sub
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Target.Rows.Count = 1 And Target.Count = 16384 Then
    Value = Target(8).Value
    Set TestRange = Target(2, 1)
    TestRow = TestRange.Row
  Else
    Value = ""
  End If
End Sub
 
Function SheetExists(Name As String) As Boolean
  Dim Counter As Long
  Counter = 1
  Do While Counter <= Worksheets.Count And Not SheetExists
    SheetExists = StrComp(Name, Worksheets(Counter).Name, vbTextCompare) = 0
    Counter = Counter + 1
  Loop
End Function

**BenoitL77** · 28/07/2020, 12h27

Messieurs, vous êtes impressionnants de réactivité et de pugnacité. J'ai à peine le temps de tester un code que paf, le suivant arrive.
Dans l'ordre :
J'ai testé ton nv code Pierre. On est d'accord que je ne dois rien changer au SelectionChange?
Je pense avoir testé tous les cas de figures : partir d'une ligne vierge ou pas, vers une ligne vierge ou pas...déplacer, couper-insérer, copier-insérer
J'ai ajouté "Value" dans le msgbox pour savoir ce qu'il veut supprimer.

Et j'ai des cas qui ne marchent pas comme il faudrait.
-Effacer le contenu d'une ligne ou supprimer une ligne qui contient qqch en H. J'ai fait en pas à pas et on ne passe pas le 1er IF (ligne 3) et on saute directement en ligne 10.
-Déplacer une ligne vierge entre 2 lignes. Dans ce cas, on passe tous les IF et il propose de supprimer l'onglet correspondant à la ligne en dessous de la destination.

Dans les autres cas, on ne passe pas soit le 1er If, soit le 3ème. Et parfois on boucle 2 fois, ce qui colle à ton explication sur le déplacement
Je n'ai pas trouvé de cas qui généraient d'ERROR

Merci
Benoit

**Pierre Fauconnier** · 28/07/2020, 14h29

Bonjour Franck,

Envoyé par pijaku

[...]
La fonction VarType renvoie, si un objet est passé ET qu’il possède une propriété par défaut, le type de la propriété par défaut de l’objet.[...]

Joli. Merci pour l'astuce. Je préfère cela à une gestion de l'erreur

**pijaku** · 28/07/2020, 14h44

De rien Pierre.

J'ai modifié mon code car, initialement, j'avais écris :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

If VarType(TestRange) = 5 Then

Or VarType(Obj) = 5 signifie (dans le cas d'un Range), qu'il contient (sa "propriété par défaut contient") un Double (VBA.vbVarType.vbDouble).

Ainsi, VarType() avec un objet Range Empty retourne 0, pour un Double 5, pour un String 8, etc...

J'ai donc remplacé par :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

If VarType(TestRange) <> 9 Then

9 correspondant à un Objet (sans propriété par défaut, donc non "instancié" [pas sur de ce mot])

**BenoitL77** · 28/07/2020, 16h54

Me revoilà,
J'ai testé le code de Franck.
J'ai bien le message quand je supprime ou efface le contenu d'une ligne.
Comme toute à l'heure j'ai testé, je crois, toutes les possibilités et ça coince sur :
-couper-insérer une ligne vierge au dessus d'une ligne "pleine" : il veut supprimer l'onglet correspondant à cette ligne pleine
-déplacer une ligne pleine vers une ligne vierge : il veut supprimer l'onglet correspondant à la ligne déplacée

Je n'ai pas eu de cas générant une erreur.

Ces codes volent un peu haut pour moi et je doute que j'arriverai à lever ces 2 points tout seul.
J'apprécie tout le mal que vous vous êtes donné jusque là et j'apprécierais d'avoir le code ultime, mais j'ai aussi une solution de replis avec les commandbar. Un essai aux US m'a permit d'avoir les intitulés adéquates. Savoir "Delete" et "Clear". Et mon code du début de cette discussion fonctionne avec des OR.

Aussi, n'hésitez pas à en rester là si vous avez mieux à faire. Et si vous voulez aller jusqu'au bout, c'est sans pb,je testerai et aurai une solution plus fiable.

Merci à vous et bonne soirée
Benoit

**pijaku** · 28/07/2020, 17h08

Je n'ai pas repris la dernière version du code de Pierre.
Peut-être pourrais-tu (de manière systématique) nous donner le code tel qu'il est écrit chez toi?

**BenoitL77** · 29/07/2020, 17h11

Bonsoir
Mince, je ne vois ton message que maintenant !
Voici mon code

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
Option Explicit
 
Private Value As String
Private TestRange As Range
Private TestRow As Long
 
Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Rows.Count = 1 And Target.Count = 16384 Then
    If VarType(TestRange) <> 9 Then                                            ' *-*-*-*-*-*-*-*-*-*-*-* TEST Objet requis
        If TestRow > TestRange.Row Or (TestRow = TestRange.Row And Target(8) = "") Then
            If SheetExists(Value) Then MsgBox ("Supprimer " & Value)
        End If
    Else
 
    End If
  End If
End Sub
 
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Target.Rows.Count = 1 And Target.Count = 16384 Then
    Value = Target(8).Value
    'MsgBox Value
    Set TestRange = Target(2, 1)
    TestRow = TestRange.Row
  Else
    Value = ""
  End If
End Sub

Merci
Benoit

**pijaku** · 30/07/2020, 08h19

Bonjour,

-couper-insérer une ligne vierge au dessus d'une ligne "pleine" : il veut supprimer l'onglet correspondant à cette ligne pleine
-déplacer une ligne pleine vers une ligne vierge : il veut supprimer l'onglet correspondant à la ligne déplacée

Définition, pour toi, de ligne pleine?
Les 16384 colonnes ? Combien?

**BenoitL77** · 30/07/2020, 09h12

Bonjour Franck
ligne "pleine" = ligne qui contient qqch en colonne H
C'est le value qu'on récupère dans le SelectionChange

Merci
Benoit

**pijaku** · 30/07/2020, 09h31

Je suis un peu perdu avec toutes ces conditions.
Pourrais-tu nous les récapituler?

**BenoitL77** · 30/07/2020, 09h58

Je comprends.

ligne vierge = rien en H
ligne pleine = qqch en H

Je voudrais, quand j'efface ou supprime une ligne pleine (et une seule), que la macro me propose de supprimer l'onglet qui a comme nom "Value".
Il faut vérifier que l'onglet "Value" existe, car il peut avoir été déjà supprimé.
Si la valeur en H est #REF, ça génère une erreur qu'il faut gérer. J'avais fait un : If IsError Then Value =""

Mais il faut que la macro me permette néanmoins de :
-insérer une ligne vierge (clic droit+insérer)
-supprimer une ligne vierge
-copier/insérer une ligne vierge ou pleine
-couper/insérer une ligne vierge ou pleine
-déplacer une ligne vierge ou pleine vers une ligne vierge
-déplacer une ligne vierge ou pleine vers une ligne pleine (qqch en H), auquel cas, on efface la ligne de destination et on doit proposer la suppression de l'onglet

Je pense avoir tout listé...
Merci

**pijaku** · 30/07/2020, 11h08

Le problème est que tu as des conditions "contradictoires".
Par exemple :
- couper/insérer une ligne pleine ==> pas de suppression
- supprimer une ligne pleine ==> suppression
Pour WorkSheet_Change(), sauf erreur de ma part (ce qui est fort possible), les 2 conditions ont le même "comportement".

**BenoitL77** · 30/07/2020, 11h30

Je vois, un couper/insérer commence par un supprimer...quel sac de nœuds!

Je crois que je vais en rester là. Ça me désole au regard du temps que vous y avez tous passé.
Si ma solution "CommandBar" venait à générer trop d'erreur, je retirerais purement et simplement cette fonctionnalité.

Néanmoins si tu veux tenter autre chose, pour le "plaisir" de trouver la solution, je testerais avec plaisir. A noter que je serais absent les 2 prochaines semaines.

Depuis Janvier, je suis impressionné par le temps consacré par tous les contributeurs pour venir en aide à des demi-ignares comme moi Et tout ça gracieusement !
Merci à tous et surtout...continuez s'il vous plait !
Benoit