Détecter/différencier un décalage de cellules via VBA

Bonjour,

Je cherche à détecter un décalage de cellules via VBA, quand on sélectionne une seule cellule (ou une plage de cellules), qu'on clique sur supprimer ou insérer, et "Décaler les cellules vers la droite" par exemple.

Workbook_SheetChange ou Worksheet_Change ne vont détecter que la cellule (ou plage de cellules) qui a été directement modifiée, mais pas toutes celles qui ont été modifiées par décalage. Et je ne peux pas différencier une simple modification de la cellule (ou plage de cellules) d'une insertion/suppression qui a provoqué un décalage à partir de cette cellule (ou plage de cellules).

Exemple en images:
Ci-dessous j'ai fait un supprimer sur la cellule B3

et je choisis de décaler les cellules vers la gauche, ce qui me donne ça

B3 a été modifiée, mais aussi C3 et D3. Mais par sheetchange, je détecter uniquement la modification de B3. Donc mon VBA ne peut pas lancer les actions adéquates sur changement de C3 et D3.

Bien sûr, je pourrais à chaque modification de cellule/plage de cellule revérifier toutes les cellules à droite (pour décaler gauche/droite) et en dessous (pour décaler haut/bas) mais si mon fichier a un million de lignes adieu les perfs. Je ne veux faire cette opération que quand je suis sûr qu'il s'agit d'un décalage, pas à chaque modification de cellules.

J'ai mis en place toute une mécanique pour différencier dans le sheetchange les simples modifications/paste, des insertions, suppressions, déplacement de lignes/colonnes entières dans la même feuille ou d'une feuille à l'autre, mais impossible de différencier la simple modification/paste d'un décalage pour le moment.

Les pistes que j'ai explorées:
- détecter le clic sur une suppression ou insert de plage de cellules qui provoquerait un décalage --> KO, pas trouvé de moyen de détecter ça

-interdire les décalage --> KO, pas trouvé comment faire ça

- trouver une propriété dans l'appel du sheetchange qui permettre de distinguer les deux --> KO, pas trouvé

- vérifier systématiquement les valeurs des cellules sous et à droite de la plage modifiée pour vérifier si elles n'ont pas changé --> cela suppose de stocker ces valeurs, donc dans l'absolu de stocker toutes valeurs d'une feuille en double, mais aussi de contrôler un grand nombre de cellules, ko pour les perfs.

Je suis à court d'idées, si vous en avez elles sont les bienvenues!

bonjour,

une proposition qui interdit le décalage (à tester)

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    lc = Cells(Target.Row, Columns.Count).End(xlToLeft).Column
    lr = Cells(Rows.Count, Target.Column).End(xlUp).Row
    nv = Target.FormulaR1C1
    Application.Undo
    lc1 = Cells(Target.Row, Columns.Count).End(xlToLeft).Column
    lr1 = Cells(Rows.Count, Target.Column).End(xlUp).Row
    If lc <> lc1 Or lr <> lr1 Then
        MsgBox "insertion/suppression de cellules non autorisée"
    Else
        If Target.Count = 1 Then
            Target.FormulaR1C1 = nv
        Else
            Target.Resize(UBound(nv, 1), UBound(nv, 2)).FormulaR1C1 = nv
        End If
    End If
    Application.EnableEvents = True
End Sub

re,

une recherche sur internet me donne ceci

https://stackoverflow.com/questions/7479721/determine-whether-user-is-adding-or-deleting-rows

Private Sub Worksheet_Change(ByVal Target As Range)

    On Error GoTo bm_Safe_Exit
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Dim olr As Long, nlr As Long, olc As Long, nlc As Long

    With Target.Parent.Cells
        nlc = .Find(what:=Chr(42), after:=.Cells(1), LookIn:=xlValues, lookat:=xlPart, _
                SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
        nlr = .Find(what:=Chr(42), after:=.Cells(1), LookIn:=xlValues, lookat:=xlPart, _
                SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        Application.Undo    'undo the last change event
        olc = .Find(what:=Chr(42), after:=.Cells(1), LookIn:=xlValues, lookat:=xlPart, _
                SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
        olr = .Find(what:=Chr(42), after:=.Cells(1), LookIn:=xlValues, lookat:=xlPart, _
                SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        Application.Repeat  'redo the last change event
    End With

    If nlr <> olr Or nlc <> olc Then
        Select Case nlr
            Case olr - 1
                Debug.Print "One (1) row has been deleted"
            Case Is < (olr - 1)
                Debug.Print (olr - nlr) & " rows have been deleted"
            Case olr + 1
                Debug.Print "One (1) row has been inserted"
            Case Is > (olr + 1)
                Debug.Print (nlr - olr) & " rows have been inserted"
            Case olr
                Debug.Print "No rows have been deleted or inserted"
            Case Else
                'don't know what else could happen
        End Select
        Select Case nlc
            Case olc - 1
                Debug.Print "One (1) column has been deleted"
            Case Is < (olc - 1)
                Debug.Print (olc - nlc) & " columns have been deleted"
            Case olc + 1
                Debug.Print "One (1) column has been inserted"
            Case Is > (olc + 1)
                Debug.Print (nlc - olc) & " columns have been inserted"
            Case olc
                Debug.Print "No columns have been deleted or inserted"
            Case Else
                'don't know what else could happen
        End Select
    Else
        'deal with standard Intersect(Target, Range) events here
    End If

bm_Safe_Exit:
    Application.EnableEvents = True
    Application.ScreenUpdating = True

End Sub

Hello H2so4,

Merci pour tes réponses, malheureusement et après essais je ne pense pas que cela marche pour ce que je veux faire. Tes deux propositions savent repérer des suppression/insertions de lignes ou colonnes complètes, mais pas les décalages apparemment. Je cherche à repérer des décalages dus à une suppression/insertion d'une plage qui n'est pas des lignes ou colonnes complètes, qui va donc décaler une partie des cellules (comme dans l'exemple) sans décaler l'ensemble.

Les deux codes que tu m'as envoyés marchent il me semble en regardant les variations de la ligne max et colonne max de la feuille excel, et certains décalages changeant les max (insertion quand ce qui a été décalé et sort de la zone max initiale n'est pas vide) mais d'autres non (autres insertions, suppressions comme celle de mon exemple).

Par contre ton truc de l'application.undo m'a inspiré sur d'autres sujets!

Bonsoir Candide Jarczak, H2so4,

Avec la macro ci-dessous, le décalage vers la gauche sera remarqué et notifié à l'utilisateur.

On place auparavant un marqueur (la lettre z ou autre) sur la ligne 1 d'une colonne hors du tableau originel.

Private Sub Worksheet_Change(ByVal Target As Range)
'Note: Tableau de données en exemple sur colonnes A à P
'Marqueur (lettre z) à inscrire une fois en colonne R sur la première ligne
'INFORMATION lors d'une suppression par Décalage vers la gauche
Lig = Target.Row
ColRef = Cells(1, "AZ").End(xlToLeft).Column
Application.EnableEvents = False
Cells(Lig, ColRef) = "z"
nb = Application.CountIf(Range(Cells(Lig, 1), Cells(Lig, ColRef)), "z")
If nb > 1 Then
    Range(Cells(Lig, ColRef), Cells(Lig, ColRef - nb - 1)) = ""
    MsgBox "Décalage effectuée vers la gauche"
End If
Application.EnableEvents = True
End Sub