La ComboBox est vide au retour dans le UseForm

Version imprimable

Bonjour à tous,

J'ai créé il y a peu un Projet VBA dans le but de rechercher des données dans un tableau suivant plusieurs critères de recherche. Le projet en est au début.

Me voici donc avec mon UserForm1 et deux modules.
Je débute en VBA donc le programme peut choquer dans la forme.

Le UseForm:

Code:

1
2
3
4
5
Public Sub UserForm_Initialize()
 
GenerateurDeComboBox "Bloc", 1
 
End Sub

Le module générateur de ComboBox:

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
Public colLettre As String
 
Public Function GenerateurDeComboBox(NomDeLaColonne, NumeroDeLaComboBox)
 
  recherche NomDeLaColonne
 
  Dim i As Integer
  Dim Collec As Collection 
  Dim cel As Range 
  Dim x As Integer 
  Dim y As String
  Dim z As String
 
  y = colLettre & "2:" & colLettre
 
  z = colLettre & "65536"
 
  Combo = "ComboBox" & NumeroDeLaComboBox
 
  Set Collec = New Collection 'définit la variable Collec
 
  Sheets("Feuil1").Activate
 
  For Each cel In Sheets("Feuil1").Range(y & Sheets("Feuil1").Range(z).End(xlUp).Row)
    On Error Resume Next
 
    Collec.Add cel.Value, CStr(cel.Value)
  Next cel
 
  For x = 1 To Collec.Count
    Combo.AddItem Collec(x)
  Next x
 
End Function

Le module de recherche:

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
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
Public Function recherche(ARechercher)
 
    Dim celluletrouvee As Range
    Dim Lig As Integer
    Dim Col As Integer
    Dim ligLettre As String
 
    Sheets("Feuil1").Activate
 
    Set celluletrouvee = Range("1:1").Find(ARechercher, LookAt:=xlPart)
 
    If celluletrouvee Is Nothing Then
    MsgBox ("pas trouvé")
    End
    Else
        Lig = celluletrouvee.Row
        Col = celluletrouvee.Column
    End If
        Select Case Col
            Case 1
                colLettre = "A"
            Case 2
                colLettre = "B"
            Case 3
                colLettre = "C"
            Case 4
                colLettre = "D"
            Case 5
                colLettre = "E"
            Case 6
                colLettre = "F"
            Case 7
                colLettre = "G"
            Case 8
                colLettre = "H"
            Case 9
                colLettre = "I"
            Case 10
                colLettre = "J"
            Case 11
                colLettre = "K"
            Case 12
                colLettre = "L"
            Case 13
                colLettre = "M"
            Case 14
                colLettre = "N"
            Case 15
                colLettre = "O"
            Case 16
                colLettre = "P"
            Case 17
                colLettre = "Q"
            Case 18
                colLettre = "R"
            Case 19
                colLettre = "S"
            Case 20
                colLettre = "T"
            Case 21
                colLettre = "U"
            Case 22
                colLettre = "V"
            Case 23
                colLettre = "W"
            Case 24
                colLettre = "X"
            Case 25
                colLettre = "Y"
            Case 26
                colLettre = "Z"
        End Select
 
End Function

Le programme fonctionne très bien si je place les 2 modules dans le UseForm.

Dans le cas ci-dessus (ou l'ensemble est partitionné) la partie qui bloque est au moment ou le programme sort de la dernière boucle "Du module génerateur de ComboBox" et retourne dans le UseForm.
A ce moment là le programme se lance, le UseForm s'ouvre normalement mais le menu déroulant de la comBoBox est vide comme si la ComBox avait perdu toutes ces entrés lors du retour au UseForm.
Plus clairement c'est ce qui arrive lorsqu'une variable n'est pas déclarée pour l'ensemble d'un projet et qu'on veut la récupèrer dans un module différent du module de déclaration. Pareil ici la ComboBox ne semble exister que dans le module ou elle a été généré.

J'espère que quelqu'un pourra me dire comment rendre la ComboBox dispo en dehors du "module génerateur de ComboBox" ou bien même s'il y a une erreur dans le code.

Merci à tous

Bonjour

Tu peux travailler directement avec le n° de colonne sans chercher la lettre correspondante.

Ci-joint proposition adaptée à mettre dans le module de ton 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
Private Sub GenerateurDeComboBox(ByVal Titre As String, ByVal NumCombo As Byte)
Dim Collec As Collection
Dim LastLig As Long, i As Long
Dim k As Integer
Dim c As Range
 
With Worksheets("Feuil1")
    Set c = .Rows(1).Find(Titre, LookIn:=xlValues, lookat:=xlPart)
    If Not c Is Nothing Then
        k = c.Column
        Set c = Nothing
        LastLig = .Cells(.Rows.Count, k).End(xlUp).Row
        Set Collec = New Collection
        For Each c In .Range(.Cells(2, k), .Cells(LastLig, k))
            On Error Resume Next
            Collec.Add c.Value, CStr(c.Value)
            On Error GoTo 0
        Next c
        Me.Controls("ComboBox" & NumCombo).Clear
        For i = 1 To Collec.Count
            Me.Controls("ComboBox" & NumCombo).AddItem Collec(i)
        Next i
        Set Collec = Nothing
    End If
End With
End Sub
 
Private Sub UserForm_Initialize()
 
GenerateurDeComboBox "Bloc", 1
End Sub

Sinon une autre méthode plus rapide qui utilise les variables tableaux et en plus on aura la possibilité de trier rapidement les items des CombobBox

(J'ai mis cette nouvelle proposition dans un nouveau post pour plus de clarté)

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
45
46
47
48
49
Private Sub GenerateurDeComboBox(ByVal Titre As String, ByVal NumCombo As Byte)
Dim LastLig As Long, i As Long, j As Long
Dim Dic As Object
Dim k As Integer
Dim c As Range
Dim Tb, Res()
 
With Worksheets("Feuil1")
    'On cherche la cellule "c" en ligne 1 qui contient le contenu de la variable Titre
    Set c = .Rows(1).Find(Titre, LookIn:=xlValues, lookat:=xlPart)
    'Si on la trouve
    If Not c Is Nothing Then
        'k désigne le n° de la colonne de cette cellule "c"
        k = c.Column
        'On détruit notre variable c
        Set c = Nothing
        'LastLig désigne le n° de ligne de la dernière cellule remplie de la colonne k
        LastLig = .Cells(.Rows.Count, k).End(xlUp).Row
        'On transfert toutes les données de cette colonne à partir de la ligne 2 vers la variable tableau Tb
        Tb = .Range(.Cells(2, k), .Cells(LastLig, k))
        'On crée une variable objet de type dictionary
        Set Dic = CreateObject("scripting.dictionary")
        'on parcours tous les éléments de notre tableau Tb
        For i = 1 To LastLig - 1
            'Si l'élément n'appartient pas encore au dictionnaire (nouvelle entrée au dico)
            If Not Dic.Exists(CStr(Tb(i, 1))) Then
                'on ajoute cet élément au dictionnaire et au tableau résultat Res
                j = j + 1
                ReDim Preserve Res(1 To j)
                Res(j) = Tb(i, 1)
                Dic.Add Tb(i, 1), CStr(Tb(i, 1))
            End If
        Next i
        'On détruit notre dictionnaire
        Set Dic = Nothing
        'On vide la combo nommée ComboBox & NumCombo et on transfert en une fois le contenu du tableau résultat sans doublons Res
        With Me.Controls("ComboBox" & NumCombo)
            .Clear
            .List = Res
        End With
    End If
End With
End Sub
 
Private Sub UserForm_Initialize()
 
GenerateurDeComboBox "Bloc", 1                'ComboBox1
GenerateurDeComboBox "Nom1", 2              'ComboBox2
End Sub

06/11/2011, 17h43
Ceresus

Merci mercatog d'avoir répondu si vite et si clairement,

Tout d'abord je tiens à dire qu'en suivant tes consignes (mettre les nouveaux codes "générateur de ComboBox" dans le UserForm avec "initialize") le code s’exécute sans broncher et me donne ce que j'attends de lui (c'est à dire l'initialisation de mon UserForm1 et le chargement de ma ou mes ComboBox), parfait.

J'ai deux question maintenant

Question 1

Je souhaite pousser un peu plus loin et aimerais comprendre pourquoi lorsque je sépare tes codes du UserForm en les mettant dans un module (par soucis de clarté) et en pensant à changer le "private" en "public" le code ne peux pas s'executer.
J'ai cru comprendre que cela venait de la partie "Me.Controls" en ligne 37 dans le 1er code et en lignes 19 et 21 dans le 2nd. Que dois-je changer pour que ça marche? Remplacer "Me" par quelque chose du style "Forms![frm Produits]"?

Question 2

Pour revenir à mon code un peu obsolète j'aimerais tout de même comprendre pourquoi la ComboBox se "décharge" en sortie de fonction. N'y a t-il pas moyen de contrer le problème?

Pour mettre le code dans un module public (afin de l'appeler par plusieurs 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
Public Sub GenerateurDeComboBox(Usf As MSForms.UserForm, ByVal Titre As String, ByVal NumCombo As Byte)
Dim LastLig As Long, i As Long, j As Long
Dim Dic As Object
Dim k As Integer
Dim c As Range
Dim Tb, Res()
 
With Worksheets("Feuil1")
    'On cherche la cellule "c" en ligne 1 qui contient le contenu de la variable Titre
    Set c = .Rows(1).Find(Titre, LookIn:=xlValues, lookat:=xlPart)
    'Si on la trouve
    If Not c Is Nothing Then
        'k désigne le n° de la colonne de cette cellule "c"
        k = c.Column
        'On détruit notre variable c
        Set c = Nothing
        'LastLig désigne le n° de ligne de la dernière cellule remplie de la colonne k
        LastLig = .Cells(.Rows.Count, k).End(xlUp).Row
        'On transfert toutes les données de cette colonne à partir de la ligne 2 vers la variable tableau Tb
        Tb = .Range(.Cells(2, k), .Cells(LastLig, k))
        'On crée une variable objet de type dictionary
        Set Dic = CreateObject("scripting.dictionary")
        'on parcours tous les éléments de notre tableau Tb
        For i = 1 To LastLig - 1
            'Si l'élément n'appartient pas encore au dictionnaire (nouvelle entrée au dico)
            If Not Dic.Exists(CStr(Tb(i, 1))) Then
                'on ajoute cet élément au dictionnaire et au tableau résultat Res
                j = j + 1
                ReDim Preserve Res(1 To j)
                Res(j) = Tb(i, 1)
                Dic.Add Tb(i, 1), CStr(Tb(i, 1))
            End If
        Next i
        'On détruit notre dictionnaire
        Set Dic = Nothing
        'On vide la combo nommée ComboBox & NumCombo et on transfert en une fois le contenu du tableau résultat sans doublons Res
        With Usf.Controls("ComboBox" & NumCombo)
            .Clear
            .List = Res
        End With
    End If
End With
End Sub

Le code sera appelé comme ceci

Code:

1
2
3
4
5
Private Sub UserForm_Initialize()
 
GenerateurDeComboBox Me, "Bloc", 1                                   'ComboBox1
GenerateurDeComboBox Me, "Nom1", 2                                   'ComboBox2
End Sub

06/11/2011, 19h51
Ceresus

Parfait! Je n'ai absolument rien à ajouter!
Le code est encore un peu compliqué pour moi mais je compte bien encore me pencher dessus pour vraiment me l'approprier.
Un grand merci mercatog :ccool: