Discussion :

[ounett]

Bonjour, je tiens par avance à remercier les personnes qui prendront de leur temps pour m'aider à résoudre ce problème.

Je suis actuellement en cours de création d'une base de donnée dans laquelle les utilisateurs pourront sélectionner différents éléments en fonction de leur souhaits.

La situation est la suivante :

Dans un premier onglet l'utilisateur va entrer un code produit dans une cellule A1 par exemple, dans une base de donnée qui se trouve sur un second onglet se code produit est rattaché à plusieurs objets.

J'aimerais qu'en fonction du code produit sélectionné par l'utilisateur, un menu déroulant s'affiche dans une autre cellule (B1) sauf que ce menu déroulant devra contenir uniquement les objets correspondant au code produit entré en A1.

Pour le moment mon programme parcours ma base de donnée et récupère dans un tableau temporaire tous les objets rattachés au code produit choisit par l'utilisateur, j'aimerai donc simplement que les nom d'objets stockés dans mon tableau deviennent les choix de la liste déroulante. J'ai essayer plusieurs méthodes mais pour le moment sans succès, peut-être que le choix de stocker les infos dans un tableau n'est pas judicieux ?

Ps : l'utilisateur va entrer des codes produits dans de très nombreuses cellules (A1,A2,A3 .... Ai) l'idéal est donc que la méthode soit facilement reproductible sur plusieurs lignes.


J'espère que mon problème est clairement décrit

Je vous remercie encore !

[Thautheme]

Bonjour Ounett, bonjour le forum,

Je te recommande l'utilisation d'une UserForm avec une TextBox1 (équivalente à ta cellule A1) et un ComboBox1 (équivalente à la liste déroulante en B1) alimentée par ton tableau. Cela devrait faciliter le code... Je peux te proposer un code si la proposition t'intéresse.

[ounett]

Bonjour thautheme,

Je ne peux pas utiliser de userform cela fais parti des contraintes malheureusement, j'aurais du le préciser dans le titre.
Merci de ton aide et j'espère que mon problème à une solution car malgré de nombreuses recherches sur différents forums je ne trouve rien pour le moment

[Thautheme]

Re,

Je te propose le code ci-dessous qui fonctionne dans les conditions suivantes :
• onglet Feuil1 où on va taper les références (ça marche pour toute la colonne A)
• onglet Feuil2 contient la base de données
- Le tableau commence an A1
- La première ligne contient les étiquettes
- La colonne A contient les références
- la colonne B contient les objets rattachés aux références

tu adapteras à ton cas...

Dans n'importe quelle cellule de la colonne A de l'onglet Feuil1, tape une référence. Si cette référence existe dans la base, dans la cellule en colonne B tu auras la liste de validation de données (liste déroulante) des objets rattachés. Attentions ! il y a une limite de caractère (que je ne connais pas) avec cette méthode...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
Private Sub Worksheet_Change(ByVal Target As Range) 'au changement dans l'onglet
Dim OB As Worksheet 'déclare la variable OB (Onglet de la Base)
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 TT As Variant 'déclare la variable TT (Tableau Temporaire)
Dim L As String 'décalre la variable L (Liste)
 
'si le changement a lieu ailleurs qu'en colonne 1 (=A), sort de la procédure (limite l'action à la colonne A)
If Target.Column <> 1 Then Exit Sub
If Selection.Cells.Count > 1 Then Exit Sub 'si plus d'une seule cellule sélectionnée, sort de la procédure
If Target.Value = "" Then Target.Offset(0, 1).Clear: Exit Sub 'si la cellule est éffacé, efface la cellule en colonne B (tout)
Set OB = Worksheets("Feuil2") 'définit l'onglet OB
TV = OB.Range("A1").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 ligne I du tableau des valeurs TV (en partant de la seconde)
    'si la valeur de la donnée (convertie en texte) ligne I colonne 1 de TV est égale
    'à la valeur (convertie en texte) de la cellule modifiée (forcément en colonne A)
    'alimente le dictionnaire D avec la valeur ligne I colonne 2 de TV
    If CStr(TV(I, 1)) = CStr(Target.Value) Then D(TV(I, 2)) = ""
Next I 'prochaine ligne de la boucle
TT = D.KEYS 'récupère dans le tableau temporaire TT la liste des éléments du dictionnaire D sans doublon
L = Join(TT, ",") 'définit la liste L
If Not L = "" Then 'condition : si la liste L n'est pas vide (ça peut arriver si aucune occurrence
'de la cellule éditée n'est trouvée dans la base de données de l'onglet O)
    With Target.Offset(0, 1).Validation 'prend en compte la validation de données de la cellue décalée d'une colonne à droite (colonne B donc)
        .Delete 'supprime une éventuelle validation de données existante
        .Add xlValidateList, Formula1:=L 'ajoute une validation de donnée avec la liste
    End With 'fin de la prise en compte de la validation de données
End If 'fin de la condition
End Sub

[ounett]

Bonjour,

Merci beaucoup !

J'ai un petit soucis en adaptant ton code, c'est probablement à cause des indices que j'ai mal modifié. Voilà ce que j'ai modifié dans ton code :

- Ligne 10 : le 1 devient 3 puisque la colonne dans laquelle l'utilisateur entre le code produit est la colonne C (le premier code produit sera dans la colonne C4, les lignes précédentes sont utilisés pour des titres)

- Ligne 12 : j'ai un premier petit doute, quand tu utilise le clear tu entre en coordonnée (0,1) ce qui dans ton cas défini la colonne B celle ou doit s'afficher la liste déroulante. Pour moi la liste déroulante va s'afficher en colonne I, dans ce cas sachant que ma colonne définit ligne 10 est la 3ème (C) mes coordonnées seront (0,6) ou (0,9) en gros l'indice doit commencer à la colonne C ou dés la colonne A. Autre petite question la liste déroulante doit également s'afficher ligne 4 et non 1 cela change t'il quelque chose ?

-Ligne 14 : au lieu de range("A1") j'ai range("C5") car l'utilisateur entrera ses codes produits de la cellule C5 à Cn

-Ligne 16 : Je changé juste mon I qui commence a 1 au lieu de 2 puisque j'ai définit mon tableau à partir de C5 et qu'il s'agit bien du premier code produit.

-Ligne 20 : "Then D(TV(I, 2))" devient "Then D(TV(I, 4))" J'ai le même doute que pour la ligne 12 est-il correct d'écrire "Then D(TV(I, 4))" ou alors "Then D(TV(I, 6))"

- Ligne 26 : les coordonnées (0,1) deviennent (0,6) Avec le même soucis qu'en ligne 20 et 12 (0,6) ou (0,9)

Je te résume juste brièvement les positions de mes infos :

- L'utilisateur entre le code produit en colonne C à partir de la ligne 4 de ma "Feuil1" et la liste déroulante devra s'afficher colonne I ligne 4 de ma "Feuil1".

- Les codes produits de ma base de donnée se trouvent également en colonne C mais commence à C5 et les objets qui alimenteront ma liste déroulante se trouve en colonne F et commence en F5.


Merci encore pour ton aide déjà précieuse.

[Thautheme]

Bonjour Ounett, bonjour le forum,

Le problème c'est que, pour définir le tableau des valeurs TV, j'utilise CurrentRegion. Ça fonctionne très bien si ce tableau comporte des étiquettes de données dans la première ligne et aucune ligne vide séparant les données. Mais tu ne me donnes pas les infos à ce sujet. Ok le tableau commence en C5 et semble ne pas contenir de ligne avec les étiquettes. Dans ce cas, il ne faut aucune ligne vide séparant les données ni aucune colonne vide séparant les données. Alors le code devient :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
Private Sub Worksheet_Change(ByVal Target As Range) 'au changement dans l'onglet
Dim OB As Worksheet 'déclare la variable OB (Onglet de la Base)
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 TT As Variant 'déclare la variable TT (Tableau Temporaire)
Dim L As String 'décalre la variable L (Liste)
 
'si le changement a lieu ailleurs qu'en colonne 3 (=C), sort de la procédure (limite l'action à la colonne C)
If Target.Column <> 3 Then Exit Sub
'si le changement a lieu dans une des trois premières lignes, sort de la procédure
If Target.Row < 4 Then Exit Sub
If Selection.Cells.Count > 1 Then Exit Sub 'si plus d'une seule cellule sélectionnée, sort de la procédure
If Target.Value = "" Then Target.Offset(0, 6).Clear: Exit Sub 'si la cellule est éffacée, efface la cellule en colonne I (tout)
Set OB = Worksheets("Feuil2") 'définit l'onglet OB
TV = OB.Range("C5").CurrentRegion 'définit le tableau des valeurs TV
Set D = CreateObject("Scripting.dictionary") 'définit le dictionnaire D
For I = 1 To UBound(TV, 1) 'boucle sur toutes les ligne I du tableau des valeurs TV
    'si la valeur de la donnée (convertie en texte) ligne I colonne 1 de TV est égale
    'à la valeur (convertie en texte) de la cellule modifiée (forcément en colonne C)
    'alimente le dictionnaire D avec la valeur ligne I colonne 4 de TV
    If CStr(TV(I, 1)) = CStr(Target.Value) Then D(TV(I, 4)) = ""
Next I 'prochaine ligne de la boucle
TT = D.KEYS 'récupère dans le tableau temporaire TT la liste des éléments du dictionnaire D sans doublon
L = Join(TT, ",") 'définit la liste L
If Not L = "" Then 'condition : si la liste L n'est pas vide (ça peut arriver si aucune occurrence
'de la cellule éditée n'est trouvée dans la base de données de l'onglet O)
    With Target.Offset(0, 6).Validation 'prend en compte la validation de données de la cellule décalée d'une colonne à droite (colonne B donc)
        .Delete 'supprime une éventuelle validation de données existante
        .Add xlValidateList, Formula1:=L 'ajoute une validation de donnée avec la liste
    End With 'fin de la prise en compte de la validation de données
End If 'fin de la condition
End Sub

Si tu peux avoir, soit des lignes soit des colonnes vides séparant les données, il faudra que tu m'indiques quelle est la dernière colonne de la base et quelle est la colonne qui est obligatoirement renseignée (pour déterminer la dernière ligne).

Je me rend compte que l'utilisation du dictionnaire D n'est indispensable que si il peut y avoir des doublons dans les objets d'une même référence. Sinon on peut s'en passer et simplifier le code...