Créer une validation de plage dynamique, Excel VBA

Créer une validation de plage dynamique, Excel VBA

Voici un code VBA détaillé pour créer une validation de plage dynamique dans Excel. Ce code va :
1. Définir une plage nommée dynamique – La liste des valeurs pour la validation s’ajustera automatiquement au fur et à mesure que des éléments sont ajoutés ou supprimés.
2. Appliquer une validation des données à une plage cible – Cela garantit que l’utilisateur ne peut sélectionner que des valeurs de la liste définie.
3. Mettre à jour la validation automatiquement – Lorsque de nouvelles données sont ajoutées, la validation se met à jour automatiquement.
Étape 1 : Comprendre la Plage Nommée Dynamique
Une plage nommée dynamique dans Excel s’ajuste automatiquement lorsque des données sont ajoutées ou supprimées. Nous pouvons la créer à l’aide de :

• La fonction OFFSET : =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)
• La fonction INDEX : =Sheet1!$A$1:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A))

Le code VBA ci-dessous va :
1. Créer une plage nommée en utilisant la fonction Formules.
2. Appliquer la validation des données à une plage cible.
Étape 2 : Implémentation du Code VBA

Sub CreateDynamicValidation()
    Dim ws As Worksheet
    Dim rngSource As Range
    Dim rngTarget As Range
    Dim lastRow As Long
    Dim nameDefined As String
    Dim validationFormula As String
    ' Définir la feuille où se trouve la liste
    Set ws = ThisWorkbook.Sheets("Sheet1")
    ' Trouver la dernière ligne avec des données dans la colonne A (liste source)
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    ' Définir la plage source dynamiquement
    Set rngSource = ws.Range("A1:A" & lastRow)
    ' Définir un nom pour la plage dynamique (modifiez-le si nécessaire)
    nameDefined = "DynamicList"
    ' Supprimer le nom existant s'il existe déjà
    On Error Resume Next
    ThisWorkbook.Names(nameDefined).Delete
    On Error GoTo 0
    ' Créer une plage nommée dynamique
    ThisWorkbook.Names.Add Name:=nameDefined, RefersTo:="=" & ws.Name & "!$A$1:INDEX(" & ws.Name & "!$A:$A,COUNTA(" & ws.Name & "!$A:$A))"
    ' Définir la plage cible où appliquer la validation (modifiez si nécessaire)
    Set rngTarget = ws.Range("C2:C20") ' Modifiez la plage si nécessaire
    ' Appliquer la validation des données en utilisant la plage nommée dynamique
    With rngTarget.Validation
        .Delete ' Supprimer la validation existante
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=" & nameDefined
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With
    MsgBox "Validation dynamique des données appliquée avec succès !", vbInformation, "Succès"
End Sub

Étape 3 : Explication du Code
1. Sélection de la feuille et de la plage source
Set ws = ThisWorkbook.Sheets("Sheet1")

• Spécifie la feuille où la liste source est stockée.

2. Trouver la dernière ligne utilisée dans la colonne A
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

• Identifie la dernière ligne utilisée dans la colonne A pour déterminer la plage dynamique.

3. Définir et créer une plage nommée dynamique
ThisWorkbook.Names.Add Name:=nameDefined, RefersTo:="=" & ws.Name & "!$A$1:INDEX(" & ws.Name & "!$A:$A,COUNTA(" & ws.Name & "!$A:$A))"

• Utilise INDEX et COUNTA pour définir une plage nommée dynamique qui grandit ou rétrécit à mesure que les données changent.

4. Sélectionner la plage cible pour la validation
Set rngTarget = ws.Range("C2:C20")

• Spécifie où la validation doit être appliquée (ici, la colonne C, lignes 2 à 20).

5. Appliquer la validation des données
With rngTarget.Validation
.Delete ' Supprimer la validation existante
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=" & nameDefined

• Supprime toute validation existante.
• Ajoute une validation de liste, garantissant que l’utilisateur peut seulement sélectionner des valeurs de la plage nommée DynamicList.

Étape 4 : Exécution du Code
1. Ouvrez Excel et créez une feuille nommée Sheet1.
2. Dans la colonne A, entrez une liste de valeurs (par exemple, Pomme, Banane, Orange).
3. Exécutez la macro VBA.
4. Essayez de sélectionner une valeur dans la colonne C2:C20 – seule une valeur de la colonne A sera autorisée.
Étape 5 : Rendre le Code Plus Dynamique

• • Au lieu de définir une plage fixe (C2:C20), utilisez :

Set rngTarget = ws.Range("C:C")

• • Cela applique la validation à toute la colonne C de manière dynamique.
  • Au lieu de coder en dur "Sheet1", permettez à l’utilisateur de sélectionner une feuille :

Set ws = ActiveSheet

• Cela permet à la macro de fonctionner sur n’importe quelle feuille active.

Conclusion
Ce code VBA gère dynamiquement la validation des données en : ✅ Mettant à jour automatiquement lorsque la liste source change
✅ Utilisant une plage nommée pour plus de flexibilité
✅ Appliquant la validation à toute plage cible spécifiée