Discussion :

[aethangaia]

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 :

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

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.

[parmi]

Bonjour,

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

Voici un exemple

Code :

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

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

[aethangaia]

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 :

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

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

[parmi]

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.

[aethangaia]

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 :

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
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.



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



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

Aëthan.

[parmi]

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.

[aethangaia]

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 :

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

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

la combobox reste vide.

merci.

[parmi]

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 :

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

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

[parmi]

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

[aethangaia]

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 :

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

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 ?

[parmi]

Ç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.

[aethangaia]

Voici donc le fichier xlsm en zip.

Merci pour ton temps et pour ton aide.

Aëthan

[parmi]

Ce n'est pas

Code :

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

Private Sub CRecherche_Initialize()

mais

Code :

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

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 :

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

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

[aethangaia]

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.