XL 2016 VBA Détecter la suppression de la dernière ligne d'un Tableau

crocrocro · 24 Juillet 2024

Bonjour,
je souhaite déclencher une macro sur toute modification dans un tableau (ici appelé TabSaisie).
Or, si l'on supprime la dernière ligne du tableau, le code ci-dessous ne le permet pas;
Normal, puisqu'après la suppression, on se retrouve sur la ligne en-dessous ... qui n'appartient pas aux tableau.

VB:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("TabSaisie")) Is Nothing Then
    ' la suppression de la dernière ligne du tableau TabSaisie n'est pas détectée
        MsgBox "Modification du Tableau TabSaisie"
    End If
End Sub

Y-a-t-il un moyen simple, autre que de tester sur les colonnes entières, d'y remédier ?
Merci d'avance.

vgendron · 24 Juillet 2024

bonjour
en utlisant un autre évènement?
Worksheet_BeforeDelete par exemple
ha non.. c'est pour la suppression de la feuille..

mromain · 24 Juillet 2024

Bonjour crocrocro, vgendron, le forum,

@crocrocro :
Tu peux tester ainsi :

VB:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("TabSaisie").ListObject.Range.Offset(1, 0)) Is Nothing Then
    ' la suppression de la dernière ligne du tableau TabSaisie n'est pas détectée
        MsgBox "Modification du Tableau TabSaisie"
    End If
End Sub

A+

crocrocro · 24 Juillet 2024

Merci mromain,
avec ton code,

VB:

If Not Intersect(Target, Range("TabSaisie").ListObject.Range.Offset(1, 0)) Is Nothing Then

on déclenche effectivement la macro si on supprime la dernière ligne,
mais aussi si on supprime la ligne immédiatement au-dessous du tableau.

mromain · 24 Juillet 2024

Rebonjour crocrocro,

Tu trouveras ci-dessous une solution "meilleure mais toujours imparfaite" :

VB:

Private Sub Worksheet_Change(ByVal Target As Range)
Static TabSaisieNbLignes As Long   'utilisation d'une variable statique pour mémoriser le nombre de ligne du tableau
    With Me.ListObjects("TabSaisie")
        If TabSaisieNbLignes = 0 Then TabSaisieNbLignes = .ListRows.Count
        If TabSaisieNbLignes <> .ListRows.Count Then
            MsgBox "ligne supprimée"
        ElseIf .ListRows.Count > 0 Then
            If Not Application.Intersect(Target, .DataBodyRange) Is Nothing Then
                MsgBox "ligne modifiée"
            End If
        End If
        TabSaisieNbLignes = .ListRows.Count
    End With
End Sub

Elle est meilleure que la précédente dans le sens où elle ne "déclenche" pas si on supprime la ligne immédiatement au-dessous du tableau.

Cependant, elle est imparfaite car elle ne "déclenche" pas si la première action réalisée sur la feuille (donc interceptée par Worksheet_Change) est une suppression de ligne.
Cela et dû au fait que la variable statique n'était pas encore initialisée.

Ce dernier souci peut être contourné en :

ajoutant cette procédure publique à la feuille contenant le tableau :

VB:

Public Sub InitWorksheetChange()
    Worksheet_Change Me.ListObjects("TabSaisie").Range(1, 1)
End Sub

appelant cette procédure à l'ouverture du classeur (avec l'évènement Workbook_Open).

A+

Gégé-45550 · 24 Juillet 2024

crocrocro à dit:
Bonjour,
je souhaite déclencher une macro sur toute modification dans un tableau (ici appelé TabSaisie).
Or, si l'on supprime la dernière ligne du tableau, le code ci-dessous ne le permet pas;
Normal, puisqu'après la suppression, on se retrouve sur la ligne en-dessous ... qui n'appartient pas aux tableau.

VB:

Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("TabSaisie")) Is Nothing Then ' la suppression de la dernière ligne du tableau TabSaisie n'est pas détectée MsgBox "Modification du Tableau TabSaisie" End If End Sub

Y-a-t-il un moyen simple, autre que de tester sur les colonnes entières, d'y remédier ?
Merci d'avance.

Bonjour,
Une idée comme ça vite fait :
Créer une variable globale au niveau de la feuille (ou une cellule dédiée quelque part dans le fichier) dans laquelle on stocke le nombre de lignes du tableau à l'initialisation de la feuille et lors de l'évènement Change.
Dans ce même évènement, avant le stockage, on teste si le nombre a changé.
Cordialement,

crocrocro · 24 Juillet 2024

merci encore mromain, merci Gégé,
je pensais qu'un code simple m'avait échappé, d'où l'ouverture de cette discussion avec la précision "Y-a-t-il un moyen simple". Il semblerait que ce ne soit pas le cas.
La première proposition de mromain qui teste l'intersection jusqu'à la ligne qui suit immédiatement le tableau est satisfaisante selon le contenu de la macro déclenchée, par exemple :
- OK pour le rafraichissement d'un TCD lié
- mais KO pour le comptage du nombre de mise à jour du tableau.
Donc à utiliser en connaissance de cause.
Je n'ai pas encore testé la 2ème proposition de mromain, qui serait donc à appliquer dans le 2ème exemple.
Je laisse encore la discussion ouverte, en attendant une improbable solution en 2 lignes de code .

crocrocro · 24 Juillet 2024

Je viens de tester la 2ème proposition de mromain :
Fonctionne bien
Et, pour ceux qui utiliseraient le code : ... à condition dans le Workbook_Open de faire l'appel à InitWorksheetChange en préfixant par le nom de la feuille pour que la macro soit reconnue.

VB:

Private Sub Workbook_Open()
    Sheets("Feuil1").InitWorksheetChange
End Sub

Comme l'a suggéré Gégé, en déclarant simplement la variable TabSaisieNbLignes Publique et non Statique, la macro InitWorksheetChange et son appel dans Workbook_Open sont inutiles.

Edit : Toujours concernant la 2ème proposition de mromain :
La variable TabSaisieNbLignesétant déclarée statique, au 1er appel, elle vaut 0, donc, pas besoin de passer par InitWorksheetChange. Le code du Worksheet_Change du post #5 est suffisant

mapomme · 24 Juillet 2024

Bonsoir à tous ,

La question est intéressante. Voilà où j'en suis...

J'ai découvert par hasard que si la dernière était vide, si on se plaçait dans la dernière cellule du tableau, si on appuie sur la touche <Tab> pour insérer une nouvelle ligne alors il faut utiliser un autre évènement que Change (qui ne se déclenche pas). J'ai donc utilisé aussi utilisé l'évènement SelectionChange.

Le code de la feuille Feuil1 :

VB:

Private Sub Worksheet_Activate()
   ThisWorkbook.Names.Add Name:="PlageTabSaisie", RefersTo:=Me.ListObjects("TabSaisie").DataBodyRange.Address
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oldAddress
   oldAddress = Replace(Mid(ThisWorkbook.Names("PlageTabSaisie").Value, 2), """", "")
   ThisWorkbook.Names.Add Name:="PlageTabSaisie", RefersTo:=Me.ListObjects("TabSaisie").DataBodyRange.Address
   If Me.ListObjects("TabSaisie").DataBodyRange.Address <> oldAddress Or Not Intersect(Target, Range("TabSaisie")) Is Nothing Then MacroSiTSmodifié
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim oldAddress
   oldAddress = Replace(Mid(ThisWorkbook.Names("PlageTabSaisie").Value, 2), """", "")
   ThisWorkbook.Names.Add Name:="PlageTabSaisie", RefersTo:=Me.ListObjects("TabSaisie").DataBodyRange.Address
   If Me.ListObjects("TabSaisie").DataBodyRange.Address <> oldAddress Then MacroSiTSmodifié
End Sub

Sub MacroSiTSmodifié()
   MsgBox "le tableau (données ou structure) a été modifié.", vbInformation
End Sub

Le code dans thisWorkbook :

VB:

Private Sub Workbook_Open()
   ThisWorkbook.Names.Add Name:="PlageTabSaisie", RefersTo:=Feuil1.ListObjects("TabSaisie").DataBodyRange.Address
End Sub

crocrocro · 24 Juillet 2024

On en découvre tous les jours, merci mapomme.
Cela me semble être une anomalie MS sur les tableaux structurés .
Conclusion pour ma part :
- Soit on monte une usine à gaz si on veut détecter une mise à jour d'un tableau et uniquement du tableau
- Soit on fait le test suivant

VB:

If Not Intersect(Target, Range("TabSaisie")) Is Nothing Then

et on risque de perdre les suppressions de la dernière ligne ou les ajouts par Tab sur la dernière cellule du tableau
- Soit on teste sur toutes les colonnes du Tableau (sans tenir compte des lignes (on ratisse plus large)
et on prendra en compte également tout ce qui est au-dessous du tableau

XL 2016 VBA Détecter la suppression de la dernière ligne d'un Tableau

crocrocro

XLDnaute Occasionnel

vgendron

XLDnaute Barbatruc

mromain

XLDnaute Barbatruc

crocrocro

XLDnaute Occasionnel

mromain

XLDnaute Barbatruc

Gégé-45550

XLDnaute Accro

crocrocro

XLDnaute Occasionnel

crocrocro

XLDnaute Occasionnel

mapomme

XLDnaute Barbatruc

Pièces jointes

crocrocro

XLDnaute Occasionnel

Discussions similaires