Créer une validation de données de plage dynamique, Excel VBA

Créer une validation de données de plage dynamique, Excel VBA

Objectif : 

Nous souhaitons créer une liste déroulante dynamique dans Excel avec VBA. Par exemple, si les valeurs valides sont dans une colonne et que cette liste peut changer (ajout ou suppression d’éléments), nous voulons que la liste déroulante s’ajuste automatiquement pour refléter la nouvelle plage de données sans avoir à la mettre à jour manuellement. 

Explication détaillée : 

1. Objectif principal : Nous allons créer une plage dynamique avec un nom défini, et cette plage changera en fonction des données présentes dans une colonne donnée. Ensuite, nous utiliserons cette plage dynamique pour alimenter une liste déroulante (validation de données) dans une cellule. 
2. Approche de la plage dynamique : Nous allons utiliser une formule dans la propriété RefersTo du nom défini pour calculer dynamiquement la taille de la plage. Nous utiliserons la fonction OFFSET et COUNTA pour calculer le nombre de cellules non vides. 

Code VBA : 

Voici un exemple de code pour créer une plage dynamique et l’utiliser dans une validation de données : 

Sub CreateDynamicValidation() 
    Dim ws As Worksheet 
    Dim lastRow As Long 
    Dim validationRange As Range 
    Dim dynamicRangeName As String 
    ' Définir la feuille et la cellule de validation 
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Modifier avec le nom de votre feuille 
    Set validationRange = ws.Range("A1") ' Modifier avec la cellule où vous souhaitez la validation 
    ' Définir le nom de la plage dynamique 
    dynamicRangeName = "DynamicList" ' Nom de la plage dynamique 
    ' Trouver la dernière ligne de données dans la colonne B (modifiez la colonne B selon vos besoins) 
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row 
    ' Créer une plage dynamique avec OFFSET et COUNTA 
    ThisWorkbook.Names.Add Name:=dynamicRangeName, RefersTo:="=OFFSET(Sheet1!$B$1, 0, 0, COUNTA(Sheet1!$B$1:$B$" & lastRow & "), 1)"  
    ' Appliquer la validation des données à la plage sélectionnée 
    With validationRange.Validation 
        .Delete ' Supprimer toute validation existante 
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ 
            xlBetween, Formula1:="=DynamicList" ' Lier la validation à la plage dynamique 
        .IgnoreBlank = True 
        .InCellDropdown = True 
        .ShowInput = True 
        .ShowError = True 
    End With 
End Sub

Explication du code : 

1. Définir la feuille et la cellule de validation : 

Set ws = ThisWorkbook.Sheets("Sheet1") 
Set validationRange = ws.Range("A1")

• Nous définissons la feuille ws et la cellule validationRange où nous voulons afficher la liste déroulante (dans ce cas, la cellule A1). 

2. Trouver la dernière ligne : 

lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row 

• Cette ligne permet de trouver la dernière ligne non vide dans la colonne B, où se trouvent les éléments de la liste déroulante. Vous pouvez remplacer « B » par la colonne de votre choix. 

3. Créer la plage dynamique : 

ThisWorkbook.Names.Add Name:=dynamicRangeName, RefersTo:="=OFFSET(Sheet1!$B$1, 0, 0, COUNTA(Sheet1!$B$1:$B$" & lastRow & "), 1)" 

• Cette ligne crée une plage dynamique nommée DynamicList. La fonction OFFSET commence à partir de la cellule B1, et la taille de la plage est ajustée en fonction du nombre de cellules non vides dans la colonne B, calculé par COUNTA. 

4. Appliquer la validation des données : 

With validationRange.Validation 
    .Delete 
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=DynamicList" 
    .IgnoreBlank = True 
    .InCellDropdown = True 
    .ShowInput = True 
    .ShowError = True 
End With

• Cette section applique la validation des données à la cellule A1. Elle supprime d’abord toute validation existante, puis ajoute une nouvelle validation de type « Liste », en utilisant la plage dynamique DynamicList. 

Remarques : 

• Vous pouvez modifier la plage validationRange pour appliquer la validation à plusieurs cellules, par exemple Range(« A1:A10 ») pour appliquer la validation à une plage de cellules. 
• Le code suppose que la liste des éléments valides se trouve dans la colonne B. Vous pouvez ajuster cette colonne en fonction de vos besoins. 

Résumé : 

Ce code permet de créer une liste déroulante dynamique dans Excel qui s’ajuste automatiquement chaque fois que vous modifiez les données de la colonne B (ajout ou suppression de valeurs). La liste déroulante dans la cellule A1 reflétera toujours la plage de données mise à jour sans nécessiter d’interventions manuelles supplémentaires.