XL 2019 VBA - Liste déroulante à partir de cellules d'une autre feuille

im_Guillaume

XLDnaute Nouveau
Bonjour,

Je suis novice en VBA et j'essaie d'implémenter la situation suivante :

Dans l'onglet 1, l'utilisateur doit choisir parmi une liste de métiers.
Cette liste se trouve dans l'onglet 2 et comprend des doublons qu'il faut ne compter qu'une fois.

Dans l'exemple ci-joint, la liste que je souhaiterais avoir serait donc : boulanger, boucher, fleuriste.

J'ai essayer de coder mais cela ne fonctionne pas :

Private Sub Worksheet_Change(ByVal target As Range)
'Liste déroulante des métiers (C5)
'Déclaration des variables
Dim Plage_Listes As Range
Dim Liste As String

'Initialisation des variables
Set Plage_Listes = Sheets("Choix").Range("C5")
Listes = Application.Goto(ActiveWorkbook.Sheets("Liste").Range("C5:C16").Value)
'Génération de la liste déroulante
With Plage_Listes.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Liste1
.IgnoreBlank = False
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With

End Sub


Merci pour toute l'aide que vous pourrez m'apporter!
 

Pièces jointes

  • Exemple1.xlsm
    17.2 KB · Affichages: 12
Solution
Bonjour Guillaume, bonjour le forum,

Ton fichier modifié en pièce jointe.
Le code principal dans le module standard Module1 :

VB:
Sub Macro1()
Dim OC As Worksheet 'déclare la variable OC (Onglet Choix)
Dim OL As Worksheet 'déclare la variable OL (Onglet Liste)
Dim CC As Range 'déclare la variable CC (Cellule Cible)
Dim TV As Variant 'déclare la variable TV (Tableau des Valeurs)
Dim D As Object 'déclare la variable D (Dictionnaire)
Dim I As Integer 'déclare la variable I (Incrément)
Dim L As String 'déclare la variable L (Liste)

Set OC = Worksheets("Choix") 'définit l'onglet OC
Set OL = Worksheets("Liste") 'définit l'onglet OL
Set CC = OC.Range("C5") 'définit la cellule cible CC
TV = OL.Range("C4").CurrentRegion 'définit le...

Robert

XLDnaute Barbatruc
Repose en paix
Bonjour Guillaume, bonjour le forum,

Ton fichier modifié en pièce jointe.
Le code principal dans le module standard Module1 :

VB:
Sub Macro1()
Dim OC As Worksheet 'déclare la variable OC (Onglet Choix)
Dim OL As Worksheet 'déclare la variable OL (Onglet Liste)
Dim CC As Range 'déclare la variable CC (Cellule Cible)
Dim TV As Variant 'déclare la variable TV (Tableau des Valeurs)
Dim D As Object 'déclare la variable D (Dictionnaire)
Dim I As Integer 'déclare la variable I (Incrément)
Dim L As String 'déclare la variable L (Liste)

Set OC = Worksheets("Choix") 'définit l'onglet OC
Set OL = Worksheets("Liste") 'définit l'onglet OL
Set CC = OC.Range("C5") 'définit la cellule cible CC
TV = OL.Range("C4").CurrentRegion 'définit le tableau des valeurs TV
Set D = CreateObject("Scripting.Dictionary") 'définit le dictionnaire D
For I = 2 To UBound(TV, 1) 'boucle sur toutes les lignes I du tableau des valeurs TV (en partant de la seconde)
    D(TV(I, 1)) = "" 'alimente le dictionnaire D avec les données en colonne 1 de TV
Next I 'prochaine ligne de la boucle
L = Join(D.Keys, ",") 'définit la liste L (la réunion de l'ensemble des éléments du dictionnaire D sans doublon (les clés), séparés par une virgule)
With CC.Validation 'prend en compote la validation de donnée de la cellule CC
    .Delete 'efface une eventuelle validation
    .Add xlValidateList, Formula1:=Lst 'ajoute la validation avec L comme Liste
End With 'fin de la prise en compte de validation de données de la cellule CC
End Sub
Pour qu'il agisse dès l'ouverture du classeur, le code dans le composant ThisWorkbook :
Code:
Private Sub Workbook_Open()
Module1.Macro1
End Sub
Et pour qu'il s'actualise si tu modifies la liste, le code dans le composant Feuil2 (Liste) :
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 Then Module1.Macro1
End Sub
 

Pièces jointes

  • Guillaume_ED_v01.xlsm
    20.5 KB · Affichages: 15

Efgé

XLDnaute Barbatruc
Bonjour @im_Guillaume , Bonjour @Robert
Si @im_Guillaume à accès à UNIQUE, une proposition par formules
une formule en feuille "Validation"
VB:
=UNIQUE(MAJUSCULE(DECALER(Liste!$C$5;;;NBVAL(Liste!$C5:$C10000))))
Et une plage nommée dynamique "ListeChoix" dans le gestionnaire de noms
Code:
=DECALER(Validation!$A$1;;;NBVAL(Validation!$A:$A))

J'ai mis en Majuscule car il y a un risque de retrouver "boucher" et "Boucher"

Cordialement
 

Pièces jointes

  • im_Guillaume_Formules.xlsx
    17.3 KB · Affichages: 9

im_Guillaume

XLDnaute Nouveau
Robert et Efgé, un grand merci pour votre réactivité et vos réponses !

Robert, je comprends votre code et il fonctionne globalement mais j'ai cependant un souci de variable : lorsque j'ouvre l'excel, voici le message d'erreur qu'il m'affiche. Savez-vous pourquoi?
1644582635091.png



Efgé, je ne sais pas si le bug est de mon côté mais je n'arrive pas à ouvrir votre document pour l'instant.
 

im_Guillaume

XLDnaute Nouveau
Bonjour Guillaume, bonjour le forum,

Ton fichier modifié en pièce jointe.
Le code principal dans le module standard Module1 :

VB:
Sub Macro1()
Dim OC As Worksheet 'déclare la variable OC (Onglet Choix)
Dim OL As Worksheet 'déclare la variable OL (Onglet Liste)
Dim CC As Range 'déclare la variable CC (Cellule Cible)
Dim TV As Variant 'déclare la variable TV (Tableau des Valeurs)
Dim D As Object 'déclare la variable D (Dictionnaire)
Dim I As Integer 'déclare la variable I (Incrément)
Dim L As String 'déclare la variable L (Liste)

Set OC = Worksheets("Choix") 'définit l'onglet OC
Set OL = Worksheets("Liste") 'définit l'onglet OL
Set CC = OC.Range("C5") 'définit la cellule cible CC
TV = OL.Range("C4").CurrentRegion 'définit le tableau des valeurs TV
Set D = CreateObject("Scripting.Dictionary") 'définit le dictionnaire D
For I = 2 To UBound(TV, 1) 'boucle sur toutes les lignes I du tableau des valeurs TV (en partant de la seconde)
    D(TV(I, 1)) = "" 'alimente le dictionnaire D avec les données en colonne 1 de TV
Next I 'prochaine ligne de la boucle
L = Join(D.Keys, ",") 'définit la liste L (la réunion de l'ensemble des éléments du dictionnaire D sans doublon (les clés), séparés par une virgule)
With CC.Validation 'prend en compote la validation de donnée de la cellule CC
    .Delete 'efface une eventuelle validation
    .Add xlValidateList, Formula1:=Lst 'ajoute la validation avec L comme Liste
End With 'fin de la prise en compte de validation de données de la cellule CC
End Sub
Pour qu'il agisse dès l'ouverture du classeur, le code dans le composant ThisWorkbook :
Code:
Private Sub Workbook_Open()
Module1.Macro1
End Sub
Et pour qu'il s'actualise si tu modifies la liste, le code dans le composant Feuil2 (Liste) :
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 Then Module1.Macro1
End Sub
Bonjour Robert,

Ayant constaté que tous les ordinateurs n'avaient pas une version d'excel à jour (permettant d'implémenter la version d'Efgé), j'ai décidé d'utiliser votre VBA.

Mais voici mon problème :
Je souhaiterais appliquer votre code à toutes les colonnes de l'onglet "Liste" et ainsi pouvoir choisir dans l'onglet "choix" :
- Un métier parmi les métier de la colonne C de l'onglet "liste"
- Un genre parmi les genres de la colonne D de l'onglet "liste"
- Un Prénom parmi les prénoms de la colonne E de l'onglet "liste".

J'ai essayé d'implémenter ça pour la colonne D mais la liste me propose les mêmes choix que la colonne C.
Identifiez-vous le problème?

Merci encore pour aide!
 

Pièces jointes

  • Guillaume_ED_v01 (2).xlsm
    21.8 KB · Affichages: 2

im_Guillaume

XLDnaute Nouveau
Merci pour votre réponse Robert!

Ne pourrait-on pas garder l'ancienne méthode en l'appliquant au nouveau cas?
Voilà 2 problèmes rencontrés avec votre nouvelle version :

- la liste déroulante de la case "choix de prénom" n'affiche pas tous les prénoms

- Lorsque je sélectionne en premier "Mme" par exemple dans liste déroulante "choix du sexe", puis que je change le "choix de métier" par la suite, mon premier choix s'efface.
Est-ce possible de tout simplement supprimer la ligne de code
" Range("D5:E5").ClearContents 'efface la plage D5:E5"
Ou cela aura d'autres répercussions?
 

Robert

XLDnaute Barbatruc
Repose en paix
Ne pourrait-on pas garder l'ancienne méthode en l'appliquant au nouveau cas?

À qui le dis-tu !... Ne pourrais-tu pas changer de cas à chaque post...

La liste n'affiche que les prénoms des personnes ayant le métier choisi et le sexe choisi (c'est le but il me semble). Je préfère effacer pour ne pas me retrouver avec un prénom qui ne devrais pas apparaître...
 

Discussions similaires

Statistiques des forums

Discussions
311 720
Messages
2 081 925
Membres
101 841
dernier inscrit
ferid87