Créer des listes de validation de données dynamiques, Excel VBA

Créer des listes de validation de données dynamiques, Excel VBA

Voici un exemple détaillé pour créer des listes de validation de données dynamiques avec VBA dans Excel. 

Objectif : 

Nous allons créer des listes de validation de données dynamiques qui se mettent à jour automatiquement en fonction des modifications des données sources. 

Étapes impliquées : 

1. Créer les données sources : Les données sources sont généralement une plage ou une liste d’éléments à partir de laquelle la liste dynamique sera peuplée. 
2. Créer la validation de données : Cela consiste à définir une règle de validation de données dans Excel, qui utilisera les données sources comme liste. 
3. Utiliser VBA pour mettre à jour la liste : Le code VBA ajustera dynamiquement la liste de validation des données en fonction des modifications de la plage de données sources. 

Exemple de scénario : 

Imaginons que nous avons une liste de catégories de produits dans Feuille1!A2:A10, et nous voulons créer une liste de validation dynamique dans Feuille2!B2, qui se mettra automatiquement à jour lorsque des éléments sont ajoutés ou supprimés de la liste source. 

Explication du code VBA détaillée : 

1. Préparer le code VBA : Nous allons écrire un code VBA pour créer automatiquement une liste de validation dynamique. 

Code VBA : 

Sub CreateDynamicDataValidation() 
    Dim wsSource As Worksheet 
    Dim wsTarget As Worksheet 
    Dim lastRow As Long 
    Dim validationRange As Range 
    Dim validationFormula As String 
    ' Définir les feuilles de calcul 
    Set wsSource = ThisWorkbook.Sheets("Sheet1") ' Feuille des données sources 
    Set wsTarget = ThisWorkbook.Sheets("Sheet2") ' Feuille de la validation de données 
    ' Trouver la dernière ligne de la liste source (en supposant que les données commencent à A2) 
    lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row 
    ' Définir la plage dynamique pour la validation 
    Set validationRange = wsSource.Range("A2:A" & lastRow) 
    ' Créer la formule pour la validation dynamique 
    ' La formule utilise OFFSET pour définir la plage dynamique 
    validationFormula = "=OFFSET(Sheet1!$A$2, 0, 0, COUNTA(Sheet1!$A$2:$A$" & lastRow & "), 1)" 
    ' Appliquer la validation des données à la cellule cible (Feuille2!B2) 
    With wsTarget.Range("B2").Validation 
        .Delete ' Supprimer toute validation existante 
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _ 
             Operator:=xlBetween, Formula1:=validationFormula 
        .IgnoreBlank = True 
        .InCellDropdown = True ' Afficher la flèche déroulante dans la cellule 
        .ShowInput = True 
        .ShowError = True 
    End With 
    MsgBox "Liste de validation dynamique créée !" 
End Sub

Explication du code : 

Définir les feuilles de calcul : 

• wsSource fait référence à la feuille contenant les données sources (dans cet exemple, « Sheet1 »). 
• wsTarget fait référence à la feuille où la validation des données sera appliquée (dans cet exemple, « Sheet2 »). 

Trouver la dernière ligne : 

• La variable lastRow est déterminée en trouvant la dernière ligne de la colonne A de la feuille source. Cela garantit que la plage est dynamique et s’adapte lorsque des données sont ajoutées ou supprimées. 

Définir la plage de validation : 

• La plage pour la validation des données est définie de manière dynamique avec wsSource.Range(« A2:A » & lastRow). Cela signifie que la plage pour la validation des données comprendra toutes les lignes de A2 à la dernière ligne contenant des données. 

Créer la formule de validation : 

• La formule utilise la fonction OFFSET pour créer une plage dynamique. OFFSET(Sheet1!$A$2, 0, 0, COUNTA(Sheet1!$A$2:$A$ » & lastRow & « ), 1) crée une plage dynamique qui s’ajuste à mesure que des lignes sont ajoutées ou supprimées dans la liste source. 
• COUNTA(Sheet1!$A$2:$A$ » & lastRow & « ) compte les cellules non vides dans la liste source et ajuste la taille de la plage en conséquence. 

Appliquer la validation des données : 

• .Validation.Delete supprime toute validation existante de la cellule cible (si elle existe). 
• .Add ajoute une nouvelle règle de validation de type xlValidateList (pour une liste déroulante). 
• .Formula1 applique la formule dynamique de validation. 
• .InCellDropdown = True garantit que la flèche déroulante apparaîtra dans la cellule cible. 
• .ShowInput et .ShowError assurent que les messages d’entrée et d’erreur seront affichés si nécessaire. 

Message de confirmation : 

• Une boîte de message est affichée pour informer l’utilisateur que la liste de validation a été créée avec succès. 

Comment utiliser ce code : 

1. Ouvrez votre fichier Excel. 
2. Appuyez sur Alt + F11 pour ouvrir l’éditeur VBA. 
3. Insérez un nouveau module en cliquant avec le bouton droit sur « VBAProject », puis en sélectionnant Insert > Module. 
4. Collez le code dans le module. 
5. Fermez l’éditeur VBA et exécutez la macro en appuyant sur Alt + F8, en sélectionnant CreateDynamicDataValidation et en cliquant sur Run. 

Remarques supplémentaires : 

• Le code suppose que les données sources commencent à la cellule A2 et vont jusqu’à la dernière cellule remplie de la colonne A. Vous pouvez ajuster les plages selon vos besoins. 
• Vous pouvez modifier la cellule cible pour la validation des données (actuellement Feuille2!B2) pour n’importe quelle cellule ou plage que vous souhaitez appliquer à la validation. 

Conclusion : 

Ce code VBA vous permet de créer une liste de validation de données dynamique qui se met à jour automatiquement lorsque les données sources changent. Cela est particulièrement utile lorsque vous avez des listes régulièrement mises à jour et que vous souhaitez éviter de modifier manuellement les règles de validation des données chaque fois que de nouvelles données sont ajoutées.