Liste déroulante dynamique

[yannlion]

Bonsoir le forum,

Après plusieurs tentatives dans différentes directions, je reviens vers vous pour voir si ce que je souhaite est faisable en VBA.

J'ai une feuille comprenant un tableau avec une colonne filière, une colonne département, une colonne catégorie et une colonne comprenant un numéro d'inscription.

Dans une autre feuille, j'aimerai qu'en fonction d'une cellule comprenant la filière, une autre le département et une autre comprenant la catégorie, une liste déroulante apparaisse dans une quatrième cellule avec les numéros d'inscriptions qui répondent aux trois premiers critères (sans doublons).

J'ai essayer de partir sur des filtres automatiques du tableau ou sur une fonction DECALER pour la liste de diffusion mais cela n'a pas abouti ...

Ci joint le fichier pour mieux comprendre la problématique.

Merci d'avance
Yannlion

 

[Cousinhub]

Re : Liste déroulante dynamique

Bonsoir,

Peut-être avec ce code, inséré dans le code de la feuille.

Pour l'utiliser, tu fais un clic droit sur le nom de l'onglet, puis "Visualiser le code"

Et dans l'éditeur, tu colles ce code :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
Dim Cel As Range, Plg As Range
Dim Fil As Object, Dep As Object, Cat As Object, Ins As Object
Dim FData As Worksheet
Set FData = Sheets("Data")
Set Fil = CreateObject("Scripting.Dictionary"): Set Dep = CreateObject("Scripting.Dictionary")
Set Cat = CreateObject("Scripting.Dictionary"): Set Ins = CreateObject("Scripting.Dictionary")
Set Plg = FData.Range("A2:A" & FData.Cells(Rows.Count, 1).End(xlUp).Row)
Select Case Target.Address
    Case "$A$3"
        Target.Offset(3).ClearContents: Target.Offset(6).Resize(1, 2).ClearContents
        For Each Cel In Plg
            Fil(Cel.Value) = Cel.Value
        Next Cel
        With Target.Validation
            .Delete
            .Add xlValidateList, Formula1:=Join(Fil.Items, ",")
        End With
    Case "$A$6"
        Target.Offset(3).Resize(1, 2).ClearContents
        Target.Validation.Delete
        If Target.Offset(-3) <> "" Then
            For Each Cel In Plg.Offset(, 5)
                If Cel.Offset(, -5) = Target.Offset(-3) Then Dep(Cel.Value) = Cel.Value
            Next Cel
            With Target.Validation
                .Delete
                .Add xlValidateList, Formula1:=Join(Dep.Items, ",")
            End With
        End If
    Case "$A$9"
        Target.Offset(, 1).ClearContents
        Target.Validation.Delete
        If Target.Offset(-6) <> "" And Target.Offset(-3) <> "" Then
            For Each Cel In Plg.Offset(, 1)
                If Cel.Offset(, -1) = Target.Offset(-6) And Cel.Offset(, 4) = Target.Offset(-3) Then Cat(Cel.Value) = Cel.Value
            Next Cel
            With Target.Validation
                .Delete
                .Add xlValidateList, Formula1:=Join(Cat.Items, ",")
            End With
        End If
    Case "$B$9"
        Target.Validation.Delete
        If Target.Offset(-6, -1) <> "" And Target.Offset(-3, -1) <> "" And Target.Offset(, -1) <> "" Then
            For Each Cel In Plg.Offset(, 2)
                If Cel.Offset(, -2) = Target.Offset(-6, -1) And Cel.Offset(, 3) = Target.Offset(-3, -1) And Cel.Offset(, -1) = Target.Offset(, -1) Then Ins(Cel.Value) = Cel.Value
            Next Cel
            With Target.Validation
                .Delete
                .Add xlValidateList, Formula1:=Join(Ins.Items, ",")
            End With
        End If
End Select
End Sub

bon courage

 

[yannlion]

Re : Liste déroulante dynamique

Tout simplement bluffant !!!
C'est exactement ça, et en plus sans avoir le moindre ralentissement ... incroyable !

Si je veux faire la même chose pour les 10 lignes suivantes de B10 à B20 à partir des catégories saisies en A10 à A20 je ressaisi le code :

Case "$A$9"
Target.Offset(, 1).ClearContents
Target.Validation.Delete
If Target.Offset(-6) <> "" And Target.Offset(-3) <> "" Then
For Each Cel In Plg.Offset(, 1)
If Cel.Offset(, -1) = Target.Offset(-6) And Cel.Offset(, 4) = Target.Offset(-3) Then Cat(Cel.Value) = Cel.Value
Next Cel
With Target.Validation
.Delete
.Add xlValidateList, Formula1:=Join(Cat.Items, ",")
End With
End If
Case "$B$9"
Target.Validation.Delete
If Target.Offset(-6, -1) <> "" And Target.Offset(-3, -1) <> "" And Target.Offset(, -1) <> "" Then
For Each Cel In Plg.Offset(, 2)
If Cel.Offset(, -2) = Target.Offset(-6, -1) And Cel.Offset(, 3) = Target.Offset(-3, -1) And Cel.Offset(, -1) = Target.Offset(, -1) Then Ins(Cel.Value) = Cel.Value
Next Cel
With Target.Validation
.Delete
.Add xlValidateList, Formula1:=Join(Ins.Items, ",")


en décalant les cibles ou possibilité de faire une boucle ?

Déjà un grand grand merci à toi bhbh !

Yannlion

 

[yannlion]

Re : Liste déroulante dynamique

Bonjour le forum,

Après avoir un peu modifié mon tableau grâce à l'aide de bhbh, je souhaiterais avoir la même chose dans les lignes en dessous A18 et B18 jusqu'en A33 et B33.

J'ai essayé de faire la boucle suivante :

    For i = 1 To 15
    Case "$A,18+i"
        Target.Offset(, 1).ClearContents
        Target.Validation.Delete
        If Target.Offset(-12 - i, 1) <> "" And Target.Offset(-10 - i, 1) <> "" Then
            For Each Cel In Plg.Offset(, 1)
                If Cel.Offset(, -1) = Target.Offset(-12 - i, 1) And Cel.Offset(, 11) = Target.Offset(-10 - 1, 1) Then Cat(Cel.Value) = Cel.Value
            Next Cel
            With Target.Validation
                .Delete
                .Add xlValidateList, Formula1:=Join(Cat.Items, ",")
            End With
        End If
    Next

Mais ça bug ... une erreur de synthase dans Case "$A,18+i" peut être ?
Et une idée pour classer automatiquement par ordre alphabétique les listes déroulantes ?

Merci
Yannlion

 

[Cousinhub]

Re : Liste déroulante dynamique

Bonsoir,

Regarde le fichier joint, j'ai fait en sorte que les données soient en ordre alphabétique dans les validation de données...

Maintenant, je ne comprends pas trop ta (tes) nouvelle(s) demande(s)

Un moment, tu parles de A10 à A20 (avec B10 : B20 associées), puis maintenant, A18 à A33 (avec B18 : B33 associées).....

Dans le fichier joint, mets en couleur, avec explications, ce que tu désires vraiment....

Bonne soirée

 

[yannlion]

Re : Liste déroulante dynamique

Oups j'ai oublié de mettre le fichier à jour ... le voici

J'ai essayé de comprendre et reprendre ta macro pour modifier l'emplacement des cellules avec un data ayant plus de colonne, je crois y être presque arrivé grâce à toi

Il ne me reste plus qu'à dupliquer les lignes en dessous c'est à dire A18 et B18 jusqu'en A33 et B33 (avec les listes déroulantes triées par ordre alphabétique).

Encore merci à toi pour ton aide.

Yannlion

 

[Cousinhub]

Re : Liste déroulante dynamique

Bonsoir,

J'ai un peu modifié la base de la macro, et utilisé un Tableau VBA, au lieu d'une Plage de cellules....

Regarde un peu, et tu me dis....


Bonne soirée

 

[yannlion]

Re : Liste déroulante dynamique

Oui c'est exactement ça !
Il y a juste un bug quand je clique sur B7 ou B9 ; à cause de la liste déroulante en B7 ?

Merci pour tout
Yannlion

 

[Cousinhub]

Re : Liste déroulante dynamique

Re-,

Presque ça, enfin "Presque"......

Effectivement, j'avais pas tout essayé, donc, erreur....

Scuse....

Regarde la v3, p'têt bien....

Bonne soirée

 

[yannlion]

Re : Liste déroulante dynamique

C'est PARFAIT !!!

Et j'ai compris plein de codes grâce à toi ! (je vais me replonger dans la macro avec nouvelle base pour comprendre la différence)

Un GRAND GRAND merci à toi pour ton aide

Yannlion

 

[Cousinhub]

Re : Liste déroulante dynamique

(je vais me replonger dans la macro avec nouvelle base pour comprendre la différence)

Yannlion

Re-,

Juste pour simplifier, j'ai mis en mémoire la base de données (Dans le code, nommé Tblo)

Ainsi, au lieu d'aller sur l'onglet vérifier les conditions cellule après cellule, je le fais dans le tableau "mémorisé"

L'accès à un tableau en mémoire est beaucoup plus rapide, qu'une recherche sur un onglet.

Bonne découverte du VBA.

Bonne (fin) de nuit