Créer des listes déroulantes dynamiques, Excel VBA

Créer des listes déroulantes dynamiques, Excel VBA

Voici une explication détaillée en français pour créer des listes déroulantes dynamiques dans Excel à l’aide de VBA. 

Étapes pour créer des listes déroulantes dynamiques avec VBA 

1. Configuration de base dans Excel

Avant de commencer avec le code VBA, vous devez préparer quelques éléments : 

• Une liste source qui contient les valeurs pour la liste déroulante (cela peut être sur une feuille séparée ou sur la même feuille). 
• Une cellule cible où vous souhaitez appliquer la validation des données (la cellule où la liste déroulante apparaîtra). 

2. Code VBA pour créer une liste déroulante dynamique

L’élément clé pour créer une liste déroulante dynamique est d’utiliser la fonctionnalité de validation des données d’Excel, qui permet de spécifier une liste de valeurs à sélectionner. Nous allons ajuster cette liste de manière dynamique avec VBA. 

Code VBA : 

Sub CreateDynamicDropDown() 
    Dim ws As Worksheet 
    Dim lastRow As Long 
    Dim sourceRange As Range 
    Dim validationCell As Range 
    Dim validationFormula As String 
    ' Définir la feuille de travail et la plage source 
    Set ws = ThisWorkbook.Sheets("Sheet1")  ' Modifier le nom de la feuille si nécessaire 
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row  ' Trouver la dernière ligne avec des données dans la colonne A 
    Set sourceRange = ws.Range("A2:A" & lastRow)  ' Modifier la plage si nécessaire 
    ' Définir la cellule cible pour la validation des données (où la liste déroulante apparaîtra) 
    Set validationCell = ws.Range("B2")  ' Modifier la cellule cible 
    ' Créer la formule dynamique pour la validation des données 
    validationFormula = "=OFFSET(Sheet1!$A$2, 0, 0, COUNTA(Sheet1!$A$2:$A$" & lastRow & "), 1)" 
    ' Supprimer toute validation existante 
    validationCell.Validation.Delete 
    ' Appliquer la validation des données avec la plage dynamique 
    validationCell.Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _ 
        Operator:=xlBetween, Formula1:=validationFormula 
    ' Facultatif : ajouter un message d'entrée ou un message d'erreur 
    validationCell.Validation.InputMessage = "Sélectionnez dans la liste" 
    validationCell.Validation.ErrorMessage = "Sélection invalide" 
    ' Confirmer que la validation a été créée 
    MsgBox "Liste déroulante dynamique créée avec succès !", vbInformation 
End Sub

Explication du code : 

1. Configuration de la feuille et de la plage source :

• On définit d’abord la feuille de travail (ws) et la plage source (sourceRange) où se trouvent les valeurs de la liste. 
• La variable lastRow est calculée avec Cells(ws.Rows.Count, « A »).End(xlUp).Row, ce qui permet de trouver la dernière ligne avec des données dans la colonne A (modifiez la colonne si nécessaire). 
• La sourceRange est définie de A2 jusqu’à la dernière ligne avec des données. 

2. Formule de validation dynamique :

• La formule OFFSET est utilisée pour créer une plage dynamique. La formule =OFFSET(Sheet1!$A$2, 0, 0, COUNTA(Sheet1!$A$2:$A$lastRow), 1) garantit que la liste déroulante s’ajuste automatiquement lorsque des données sont ajoutées ou supprimées dans la plage source.  
• Sheet1!$A$2 : Point de départ de la liste. 
• COUNTA(Sheet1!$A$2:$A$lastRow) : Compte le nombre de cellules non vides dans la colonne A (ajustez si votre liste contient des cellules vides ou d’autres critères). 
• 1 : Cela représente la largeur de la plage, donc seule une colonne est prise en compte. 

3. Application de la validation des données :

• La cellule cible (validationCell) est définie comme celle où la liste déroulante apparaîtra (ici, B2). 
• La méthode validationCell.Validation.Add permet d’ajouter la validation des données, où :  
• Type:=xlValidateList : Spécifie que le type de validation est une liste. 
• Formula1:=validationFormula : Utilise la formule dynamique créée précédemment pour la liste. 

4. Personnalisation facultative :

• Vous pouvez personnaliser le message d’entrée et le message d’erreur pour guider l’utilisateur. 
• Les lignes validationCell.Validation.InputMessage et validationCell.Validation.ErrorMessage permettent d’afficher des messages utiles lorsque l’utilisateur sélectionne la cellule. 

5. Exécution du macro :

• Lorsque vous exécutez ce macro, une liste déroulante dynamique sera automatiquement créée dans la cellule cible (B2 ici). La liste déroulante s’ajustera automatiquement en fonction du nombre d’éléments dans la plage source (colonne A). 

Test et ajustements : 

• Assurez-vous que votre plage source est correctement remplie. La liste déroulante dynamique reflétera automatiquement tout changement dans cette liste (ajouts ou suppressions). 
• Vous pouvez modifier la cellule cible ou la plage source en ajustant les variables validationCell et sourceRange dans le code. 

Conclusion : 

L’utilisation de VBA pour créer des listes déroulantes dynamiques dans Excel permet d’automatiser la mise à jour des listes. Cette méthode est particulièrement utile lorsque les valeurs de la liste changent fréquemment et garantit que les utilisateurs disposent toujours des options les plus récentes dans leurs listes déroulantes.