Erreur Worksheet_change double IF entremêlés

Bonjour la compagnie,

Dans une application que j'ai besoin de développer, j'ai besoin de pouvoir réinitialiser la taille d'une zone prédéfinie à partir d'une valeur contenue dans une cellule de cette même zone. Pour ce faire j'ai donc eu recours à la sub Worksheet_change et aux named range pour les différentes zones et cellules qui activent la sub. Ma manière de fonctionner est généralement de créer des named range qui commecent toujours par le même string auquel un numéro est ajouté afin de pouvoir rapidement identifier l'objet des named range et les zones auxquelles elles sont reliées.

Vous trouverez ci-dessous la fonction que j'essaie d'implémenter. De façon générale, la sub fonctionne mais l'étape 3, contenant un IF supplémentaire semble poser problème et je n'arrive pour le moment pas à en idenfier la cause (et donc la solution). Je joins également un fichier d'exemple. J'ai essayé de commenter le code au maximum afin que cela soit compréhensible.

Si quelqu'un a une idée de la manière de résoudre cela, je lui serai très reconnaissant.

J'espère avoir exposé mon problème clairement, je sais que je ne suis pas des plus doués pour cela :/

Private Sub Worksheet_Change(ByVal Target As Range)
'But de la Sub:
'Chaque Zone (nommée Zone_1, Zone_2, etc...) a une taille initiale
'Disons 10 lignes sur 9 colonnes
'Dès qu'une valeur est entrée dans la "Trigger" zone (nommée Trigger_1, Trigger_2, etc...)
'Une opération (encore non programmée) va modifier la taille de la zone, ajouter des tables, etc.
'Avant que ceci se déroule, il faut réinitialiser la taille de la zone à la taille initiale

'Etape 0 : Définir la taille de la zone initiale
Dim DRC As Integer 'Default Rows Count
Dim DCC As Integer 'Default Col Count
DRC = 10 'Row Count for Default Range
DCC = 9  'Column Count for default Range

'Etape 1: il faut identifier la zone qui va être concernée (1,2,3 ...)
Dim x As Integer 'x est l'index qui va l'identifier

If (Target.Name.Name Like "Trigger_*" = True) Then 'Si le nom de la cellule changée commence bien par Trigger alors on modifie
        x = onlyDigits(Target.Name.Name) 'On retrouve x avec une user-defined fonction qui retrouve les chiffres à la fin du string (voir plus bas)
'Etape 2 : On calcule la taille de la zone dans l'état actuel
        Dim CRC As Integer, CCC As Integer 'current Row count and current col counts
        CRC = Range("Zone_" & x).Rows.Count
        CCC = Range("Zone_" & x).Columns.Count

'Etape 3: on supprime les lignes excédentaires à la zone initiale
'C'est cette partie du code qui pose visiblement problème
        If CRC > DRC Then 'Si le # de ligne actuel est supérieur au nombre de ligne de la zone initiale
            With Range("Zone_" & x) 'Alors dans cette zone on opère la suppression
                Range(.Cells(DRC + 1, 1), .Cells(CRC, TCC)).EntireRow.Delete 'suprresion des lignes
              'Normalement la zone nommée est automatiquement réajustée dans le dictionnaire
            End With
        End If
'Etape 4: on établit ensuite les modifications désirées en fonction de la valeur prise dans la cellule Trigger
        If Target.Value = "Constant" Then
           MsgBox "Hello, insert your code here"
        End If
Else
    Exit Sub 'Nécessaire?
End If 'Fin du conditionnement sur le nom de la cible
End Sub

Function onlyDigits(s As String) As String
    ' Variables needed (remember to use "option explicit").   '
    Dim retval As String    ' This is the return string.      '
    Dim i As Integer        ' Counter for character position. '
    ' Initialise return string to empty                       '
    retval = ""
    ' For every character in input string, copy digits to     '
    '   return string.                                        '
    For i = 1 To Len(s)
        If Mid(s, i, 1) >= "0" And Mid(s, i, 1) <= "9" Then
            retval = retval + Mid(s, i, 1)
        End If
    Next
    ' Then return the return string.                          '
    onlyDigits = retval
End Function

Bonjour,

lorsque tu fais un delete dans ta feuille tu actives worksheet_change ...

adapte cette partie du code ainsi

            With Range("Zone_" & x) 'Alors dans cette zone on opère la suppression
                Application.EnableEvents = False 'désactive la gestion d'événements
                Range(.Cells(DRC + 1, 1), .Cells(CRC, TCC)).EntireRow.Delete 'suprresion des lignes
                Application.EnableEvents = True 'réactive la gestion d'événements
                'Normalement la zone nommée est automatiquement réajustée dans le dictionnaire
            End With

Ah merci! Je n'y ai pas pensé car je pensais que justement ca n'activerait ps la ligne de commande. Ca règle bien mon problème. Un tout grand merci pour ton temps :)

Il y a cependant quelque chose que je ne suis pas tout à fait sûr de comprendre avec les events...

Désormais dans ma feuille, si je modifie une autre cellule qui n'a pas le nom de la cellule target, alors il me remet un message d'erreur sur la ligne

If (Target.name.name Like "Trigger_*" = True) Then

Y a-t-il un moyen de bypasser ces messages d'erreur sans devoir placer des Application.enables.event = True/False partout? Et pourquoi affiche-t-il l'erreur alors même que le nom de la cellule activée n'est pas celui mentionné dans la condition première?

re-bonjour,

tu reçois un message d'erreur car la cellule modifiée (target) n'a pas de nom.

c'est en général une bonne pratique d'exclure en début de procédure tous les ranges sur lesquels ne s'applique pas le code,(ou inversément d'inclure tous les ranges sur lesquels s'applique le code).

if not intersect(target,range("D1:D1000")) then exit sub 'on sort si target n'est pas dans d1:D1000
if target.count>1 then exit sub ' on sort si nombre de cellules impactées est > 1
if target.column<> 4 then exit sub ' on sort si pas en colonne D
if target.row < 10 then exit sub 'on sort si N° de ligne inférieur à 10

Merci de ta réponse h2so4. Je pensais cependant, avoir exlu les cellules ne répondant pas en critère en utilisant justement le If statement mentionné

If (Target.name.name Like "Trigger_*" = True) Then

J'ai un nombre indéterminé de cellules pouvant être justement un trigger à des endroits que je ne suis pas en mesure de prévoir également. Comment puis-je adapter ma formule avec les named range? J'ai essayé la formulation suivante mais cela ne plaît pas au logiciel

if not intersect(target.name, (ThisWorbook.names like "Trigger_*" = true)) then 

Je suis désolé de poser toutes ces questions mais ma connaissance de VBa est assez rudimentaire =/

bonsoir,

la plupart des cellules ne sont pas nommées et donc cela provoque une erreur lorsque l'on teste leur nom.

dans ce cas, la meilleure solution est de gérer ce cas d'erreur.

nom = ""
On Error Resume Next
nom = Target.Name.Name
On Error GoTo 0
If (nom Like "Trigger_*" = True) Then 'Si le nom de la cellule changée commence bien par Trigger alors on modifie
        x = onlyDigits(nom) 'On retrouve x avec une user-defined fonction qui retrouve les chiffres à la fin du string (voir plus bas)

Oh god, thanks! Cela fonctionne correctement désormais. Un tout grand merci :)