Alimenter Combobox dans Userform avec noms définis variables

Bonjour,

Nouvel inscrit sur ce forum, je requiers aujourd'hui votre aide pour un classeur Excel 2010.

J'ai une feuille1 sur laquelle sont enregistrés en colonne A (à partir de A2) des N° de série (chiffres uniquement) ayant tous une structure identique, à savoir : "XXXXXXXX". Les deux premiers chiffres correspondent à l'année de fabrication d'un article, les deux suivants désignent la famille de l'article et enfin les quatre derniers indiquent le numéro de la série (0000 pour les prototypes, 0001 à 9999 pour les articles vendus).

Un N° de série "14010123" indique donc que l'article a été fabriqué en 2014, qu'il s'agit d'un aspirateur et que c'est le 123ème de la série.

Dans une feuille BDDarticles, j'ai répertorié la liste de tous mes articles en 3 colonnes. A=Famille B=code article C=Nom de l'article.

Dans la famille des aspirateurs ("01") il peut y avoir x articles différents ayant chacun un code article unique (j'ai 54 familles d'articles avec le même fonctionnement soit plus de 160 articles au total répartis entre elles)

Ainsi, ma feuille BDDarticles ressemble à

01 5151624 Aspirateur1
01 1201589 Aspirateur2
01 0901658 Aspirateur3
02 1201358 Téléphone1
02 1201359 Téléphone2
03 1301569 Téléviseur1
04 1301274 Téléscope1
04 1301275 Téléscope2
04 1301276 Téléscope3
04 1301277 Téléscope4
04 1301311 Téléscope5
04 1301312 Téléscope6

J'ai défini une plage de nom pour chaque famille d'article "code01", "code02", "code03"... jusqu'à "code54"

Mon problème est le suivant :

Via des userforms, je peux créer, modifier ou supprimer des fiches N° de série. Dans le Userfom qui me sert à créer une nouvelle fiche, j'entre dans une textbox le nouveau N° de série d'un article vendu. Dans une combobox, je liste automatiquement les articles qui appartiennent à la famille du N° de série entré (par le nom défini qui correspond) afin de réduire le choix et d'éviter toute erreur de saisie. En soit, tout fonctionne bien.

J'utilise :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13

Private Sub TextBox1_Change() 'OK Dim cdepdt As String cdepdt = Mid(TextBox1.Value, 3, 2) With Sheets("BDDarticles") If cdepdt = "01" Then Me.ComboBox4.RowSource = ("code01") 'If cdepdt = "02" Then Me.ComboBox4.RowSource = ("code02") 'If cdepdt = "03" Then Me.ComboBox4.RowSource = ("code03") If cdepdt = "04" Then Me.ComboBox4.RowSource = ("code04") ... If cdepdt = "54" Then Me.ComboBox4.RowSource = ("code54) End With End Sub

---

Vu que le nombre d'article augmente régulièrement, même si le nombre de familles lui reste le même, je suis donc amené à allongé ma liste d'articles et par conséquent à redéfinir le range de cellules du nom défini pour chaque famille.

Je souhaiterais pouvoir créer un nouvel article automatiquement ajouté au nom défini qui lui correspond, sans avoir à trier la colonne famille, ou l'insérer à la suite du dernier de sa famille. Comment puis-je faire cela de façon dynamique, en spécifiant que si le nouvel article entré a pour famille "01", il fera automatiquement partie du nom défini "code01" et remontera ainsi automatiquement dans la liste de la combobox4 de mon Userform ?

Enfin, dans un autre Userform qui permet de faire une recherche par N° de série via un combobox, je souhaite lister dans mon combobox uniquement les numéros de série avec pour code famille "01".

N'hésitez pas à me demander des explications si je n'ai pas été suffisamment clair et d'avance merci pour vos réponses et pour votre aide.

Aëthan.

Bonjour,

Plutôt que de nommer des plages, si tu utilisais des filtres ?

Voici un exemple

Code:

---

1 2 3 4 5 6 7 8 9 10 11

Dim nbLignes As Long   'nombre de lignes en colonne A nbLignes = Sheets("Feuil1").Cells(Rows.Count, "A").End(xlUp).Row   'enlève les filtres Sheets("Feuil1").AutoFilterMode = False 'filtre la colonne 1 sur le critère 04 Sheets("Feuil1").Rows(1).AutoFilter Field:=1, Criteria1:="04" 'Charge le combobox ou le listbox Me.ListBox1.List = Sheets("Feuil1").Range("A2:A" & nbLignes).SpecialCells(xlCellTypeVisible).Value

---

Change les noms de feuille et de colonne au besoin

Parmi,

Avant toute chose, merci pour ta réponse.

J'ai appliqué ta méthode, mais j'ai sans doute fait une erreur dans la mesure où quand je lance mon userform et que j'ouvre la combobox, la liste reste complète, sans tri et lorsque je clique sur une ligne, j'obtiens un message d'erreur "Erreur d'exécution '1004' : La méthode AutoFilter de la classe Range a échoué"

Peut-être l'ai-je inséré au mauvais endroit ?

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13

Private Sub ComboBox1_Change() '[AZ1] = CRecherche.ComboBox1 Dim nbLignes As Long 'nombre de lignes en colonne A nbLignes = Sheets("BDDarticles").Cells(Rows.Count, "A").End(xlUp).Row   'enlève les filtres Sheets("BDDarticles").AutoFilterMode = False 'filtre la colonne 1 sur le critère 46 Sheets("BDDarticles").Rows(1).AutoFilter Field:=1, Criteria1:="46" 'Charge le combobox ou le listbox Me.ComboBox1.List = Sheets("BDDarticles").Range("A2:A" & nbLignes).SpecialCells(xlCellTypeVisible).Value End Sub

---

Merci pour ton aide.

Aëthan

Effectivement.
Si le combobox doit être rempli à l'ouverture du Userform, met le code dans Userform_Initialize
S'il doit être rempli lors d'une action quelconque, insère-le lors de cette action.

Parmi,

Merci à nouveau.

J'ai donc suivi ta consigne, mais la combobox1 reste vide. Faut-il remplir le champ rowsource dans ses propriétés ?

Voici le code complet du userform :

Code:

---

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 34 35 36 37 38 39 40 41 42 43 44

Private Sub ok_Click() Dim Cel As Range, Lig&, i& If ComboBox1 <> "" Then Set Cel = Feuil1.Range("A2:A" & Feuil1.Range("A" & Rows.Count).End(xlUp).Row).Find(ComboBox1, , , xlWhole) If Not Cel Is Nothing Then Lig = Cel.Row Else MsgBox "Aucune correspondance trouvée", , "Pas de Corespondance": Exit Sub CSaisir.TextBox11 = Feuil1.Cells(Lig, 1) CSaisir.TextBox12 = Feuil1.Cells(Lig, 4) CSaisir.TextBox13 = Feuil1.Cells(Lig, 5) CSaisir.TextBox14 = Feuil1.Cells(Lig, 6) CSaisir.TextBox15 = Feuil1.Cells(Lig, 7) CSaisir.TextBox16 = Feuil1.Cells(Lig, 8) CSaisir.TextBox17 = Feuil1.Cells(Lig, 10) CSaisir.TextBox18 = Feuil1.Cells(Lig, 8) CSaisir.TextBox19 = Feuil1.Cells(Lig, 2) CSaisir.TextBox20 = Feuil1.Cells(Lig, 3) CSaisir.TextBox21 = Feuil1.Cells(Lig, 3) + 729 CSaisir.TextBox22 = Feuil1.Cells(Lig, 24) CSaisir.TextBox23 = Feuil1.Cells(Lig, 25) CSaisir.TextBox24 = Feuil1.Cells(Lig, 10) CSaisir.TextBox25 = Feuil1.Cells(Lig, 11) CSaisir.TextBox26 = Feuil1.Cells(Lig, 12) CSaisir.TextBox27 = Feuil1.Cells(Lig, 13) CSaisir.TextBox28 = Feuil1.Cells(Lig, 14) CSaisir.TextBox29 = Feuil1.Cells(Lig, 15) CSaisir.TextBox30 = Feuil1.Cells(Lig, 16) CSaisir.Show End If End Sub   Private Sub RetourAccueil_Click() Accueil.Show End Sub   Private Sub CRecherche_Initialize() Dim nbLignes As Long 'nombre de lignes en colonne A nbLignes = Sheets("Recap").Cells(Rows.Count, "A").End(xlUp).Row 'enlève les filtres Sheets("Recap").AutoFilterMode = False 'filtre la colonne 1 sur le critère 46 Sheets("Recap").Rows(1).AutoFilter Field:=1, Criteria1:="46" 'Charge le combobox ou le listbox Me.ComboBox1.List = Sheets("Recap").Range("A2:A" & nbLignes).SpecialCells(xlCellTypeVisible).Value End Sub

---

et un screenshot de celui ci avec la combobox1 sélectionnée.

Pièce jointe 149757

Enfin, un screenshot de la feuille Recap avec un fragment de la liste des N° de série en colonne A

Pièce jointe 149762

Peux-tu me dire si j'ai loupé quelque chose ? d'avance merci pour ton temps et tes réponses.

Aëthan.

Dans Initialize, tu recherches 46 comme valeur de filtre, mais ta colonne contient des nombres (en texte...) de 8 caractères.
Dans ton premier message, tu disais que tu avais séparé ces nombres en parties. Ce serait sur ces parties qu'il faudrait exercer le filtre.
46 ne sera pas trouvé dans 14460232. En fait il pourrait être trouvé en bouclant mais pas en filtrant... Si tu filtres en utilisant "contient 46", il te trouvera tous les nombres qui contiennent ce nombre, que ce soit au milieu ou à la fin.

Ok je vois.

Idéalement, ce que j'ai besoin de faire, c'est d'isoler les deux premiers chiffres des N° de série afin d'effectuer un "filtre" sur les chiffres en 3ème et 4ème position uniquement. Ex : 14460232. Ainsi, dans ma combobox, je pourrais voir apparaître uniquement la liste des N° de série sous la forme "XX46XXXX" (ou 01 ou 02 ou 03... jusqu'à 54)

Peut-être avec la fonction mid ?

Tu parles de boucler, ça sous entend que j'insère une fonction for ou while, c'est bien ça ?

J'ai l'impression qu'il y a une erreur dans mon userform puisque même en mettant un simple :

Code:

---

1 2 3

Private Sub CRecherche_Initialize() Me.ComboBox1.RowSource = "Recap!$A$2:$A$54" End Sub

---

la combobox reste vide.

merci.

Si tu ne peux pas insérer des colonnes pour inscrire les parties des numéro de séries, il faudrait boucler un peu comme ceci

Code:

---

1 2 3 4 5 6 7

Dim I as Long, nbLignes as Long nbLignes = Sheets("Recap").Cells(Rows.Count, "A").End(xlUp).Row For I = 2 to nbLignes If Mid(Sheets("Recap").Range("A" & I),3,2)="46" then 'ici tu peux mettre une variable MonUserform.combobox1.additem Sheets("Recap").Range("A" & I) end if Next

---

Et oublie le RowSource qu'il faudrait modifier continuellement...

Merci Parmi,

Je pense que la cette boucle correspond parfaitement à mon besoin.

En revanche, j'ignore pour quelle raison la combobox reste invariablement vide même avec une fonction aussi simple que :

Code:

---

1 2 3

Private Sub CRecherche_Initialize() Me.ComboBox1.RowSource = "Recap!$A$2:$A$54" End Sub

---

Aurais-tu une idée ?

Puis-je t'envoyer mon fichier ?

Ça devrait fonctionner.
Je viens de faire le test avec des données sur Feuil1 et ça passe très bien.
Le premier élément n'est pas visible, mais la liste est remplie.

Si tes données ne sont pas confidentielles, tu peux le mettre sur le site.
Si c'est un xlsm, il faut le zipper autrement, ça passe.

Voici donc le fichier xlsm en zip.

Merci pour ton temps et pour ton aide.

Aëthan

Ce n'est pas

Code:

---

Private Sub CRecherche_Initialize()

---

mais

Code:

---

Private Sub Userform_Initialize()

---

à mettre dans le Userform...

PS: il y a une erreur à l'ouverture de ton fichier

Dans le userform CRecherche, change le Initialize comme suit pour li re dans la feuille Recap

Code:

---

1 2 3 4 5 6 7 8 9 10

Private Sub Userform_Initialize() Dim I As Long, nbLignes As Long nbLignes = Sheets("Recap").Cells(Rows.Count, "A").End(xlUp).Row For I = 2 To nbLignes If Mid$(Sheets("Recap").Range("A" & I), 3, 2) = "46" Then 'ici tu peux mettre une variable Me.ComboBox1.AddItem Sheets("Recap").Range("A" & I) End If Next 'Me.ComboBox1.RowSource = "Recap!$A$2:$A$54" End Sub

---

Ça devrait fonctionner

C'est parfait !

Je vais maintenant tenter d'adapter cette méthode à d'autres combobox.

Merci infiniment Parmi pour ton aide et pour ton temps.