Créer un système de surveillance d’une plage dynamique, Excel VBA

Créer un système de surveillance d’une plage dynamique, Excel VBA

Voici une explication détaillée et un code VBA pour créer un système de surveillance d’une plage dynamique dans Excel. Cet exemple vous permet de suivre les modifications dans une plage spécifiée, avec un suivi en temps réel et des actions déclenchées à chaque modification. 

Surveillance d’une Plage Dynamique avec VBA 

Dans Excel, une plage dynamique est une plage dont la taille change en fonction des données qu’elle contient. Par exemple, vous pouvez vouloir surveiller une plage de cellules dans une feuille de calcul, où la taille de la plage peut s’étendre ou se contracter en fonction des données saisies. Le but de ce code est de surveiller cette plage dynamique et d’effectuer une action chaque fois qu’une modification est détectée. 

Étapes : 

1. Définir une plage dynamique. 
2. Surveiller les modifications dans la plage. 
3. Déclencher des actions lors des modifications. 

Code VBA pour la Surveillance d’une Plage Dynamique : 

Le code suivant permet de surveiller les modifications dans une plage dynamique et de déclencher une action spécifique (par exemple, afficher une boîte de message lorsqu’une donnée change dans la plage surveillée). 

1. Définir l’événement Worksheet Change 

Nous allons utiliser l’événement Worksheet_Change pour surveiller toute modification dans la feuille de calcul. 

2. Définir la Plage Dynamique 

Nous définirons la plage dynamique en calculant la dernière ligne et la dernière colonne contenant des données dans une feuille spécifique. Cela permettra à la plage de s’étendre ou de se contracter au besoin. 

3. Suivre les Modifications 

Chaque fois qu’une modification est effectuée dans la plage dynamique, l’événement déclenchera une action spécifique. 

Private Sub Worksheet_Change(ByVal Target As Range) 
    ' Déclaration des variables 
    Dim DynamicRange As Range 
    Dim LastRow As Long 
    Dim LastColumn As Long 
    Dim MonitorRange As Range 
    ' Trouver la dernière ligne et la dernière colonne de la feuille 
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row ' Changez "A" pour la colonne de votre choix 
    LastColumn = Cells(1, Columns.Count).End(xlToLeft).Column ' Vous pouvez spécifier la colonne ici aussi 
    ' Définir la plage dynamique (A1 jusqu'à la dernière cellule avec des données) 
    Set DynamicRange = Range("A1").Resize(LastRow, LastColumn) ' Vous pouvez ajuster le point de départ (A1) si nécessaire 
    ' Vérifier si le changement se trouve dans la plage dynamique 
    If Not Intersect(Target, DynamicRange) Is Nothing Then 
        ' Effectuer l'action souhaitée lorsqu'une modification se produit 
        MsgBox "Une modification a eu lieu dans la plage dynamique !" & vbCrLf & "La cellule " & Target.Address & " a été modifiée." 
        ' Exemple d'action supplémentaire : Vous pouvez mettre à jour d'autres plages ou déclencher une logique plus complexe 
        ' Exemple : Écrire la date actuelle dans la cellule "Z1" lorsqu'une modification se produit 
        Range("Z1").Value = "Dernière modification le : " & Now 
    End If 
End Sub

Explication : 

1. Définition de la Plage Dynamique : 

• Nous calculons LastRow et LastColumn pour définir la taille de la plage à surveiller.  
• LastRow = Cells(Rows.Count, "A").End(xlUp).Row trouve la dernière ligne de la colonne A qui contient des données. 
• LastColumn = Cells(1, Columns.Count).End(xlToLeft).Column trouve la dernière colonne contenant des données dans la première ligne. 
• Ensuite, nous définissons la plage dynamique depuis la cellule A1 jusqu’à la cellule en bas à droite, en utilisant la méthode .Resize(). 

2. Surveillance des Modifications : 

• L’événement Worksheet_Change s’exécute chaque fois qu’une modification est effectuée dans la feuille de calcul. 
• If Not Intersect(Target, DynamicRange) Is Nothing Then vérifie si la cellule modifiée (Target) se trouve dans la plage dynamique que nous avons définie. 
• Si c’est le cas, le code déclenche une action : dans ce cas, une boîte de message s’affiche avec les détails de la cellule modifiée. 

3. Déclenchement des Actions : 

• Dans cet exemple, une boîte de message s’affiche lorsque la cellule modifiée se trouve dans la plage dynamique. 
• Vous pouvez remplacer cette action par toute autre tâche, comme mettre à jour une autre plage de cellules, enregistrer un log dans une autre feuille, ou effectuer des calculs. 

Personnalisation : 

• Sélection de la Plage : La plage commence actuellement à partir de la cellule A1. Vous pouvez modifier ce point de départ ou ajuster les définitions de colonnes et de lignes selon vos besoins. 
• Action lors de la Modification : Le code affiche une boîte de message, mais vous pouvez remplacer cela par d’autres actions comme la mise à jour d’une autre cellule, l’enregistrement d’une entrée dans un fichier journal, ou l’envoi d’un e-mail. 
• Extension/Contrat de la Plage : La plage s’étendra ou se contractera automatiquement lorsque de nouvelles données seront ajoutées ou supprimées dans la feuille de calcul. 

Considérations Finales : 

• Cette méthode utilise l’événement Worksheet_Change, qui est déclenché à chaque modification. Assurez-vous que les actions que vous effectuez sont efficaces afin d’éviter des ralentissements, surtout si la plage surveillée est grande ou si les modifications sont fréquentes. 
• Vous pouvez ajouter des vérifications supplémentaires pour affiner l’action, comme surveiller uniquement des cellules spécifiques ou vérifier le type de modification (par exemple, valeur vs formule). 

Cette approche vous permet de créer un système de surveillance dynamique robuste dans Excel en utilisant VBA.