Combobox va et vient

Version imprimable

Bonjour au forum,

j'aurai besoin d'aide... J'ai 2 combobox dans un userform.
Lorsque je choisis dans le combobox2 un item alors dans le combobox11 doit charger une liste d'article relative au choix du combobox2.
Lorsque je choisis dans le combobox11 un item alors dans le combobox2 doit être chargé une liste d'article liée à l'item choisit du combobox11
En quelque sort un va et vient. Mes combobox sont chargés depuis un fichier TXT.

Je galère entre les combobox_change et enter (ce qui est déjà un 1er problème)
Je suis novice. Qui veut et peut m'aider sur mon problème ?
Par avance merci.

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
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
Private Sub ComboBox2_enter()
Dim Nom(2, 50) As String 
Dim Client(50), Modele(10), Aspect(10), Pupille(10), PConnecteur(10), CConnecteur(10), Hydraulique(10), Abouts(10), Vis(10), Vidange(10), Revision(100), Article(10) As String
Dim i As Integer
Dim chaine As String
 
Call OuvreFichierTXT("PARAM.txt", Nom, Client, Modele, Aspect, Pupille, PConnecteur, CConnecteur, Hydraulique, Abouts, Vis, Vidange, Revision, Article)
 
ComboBox2.AddItem ""
For i = 0 To UBound(Modele)
If Modele(i) <> "" Then
ComboBox2.AddItem Modele(i)
End If
Next i
End Sub
 
 
'Private Sub ComboBox2_enter()
'Dim Nom(2, 50) As String
'Dim Client(50), Modele(10), Aspect(10), Pupille(10), PConnecteur(10), CConnecteur(10), Hydraulique(10), Abouts(10), Vis(10), Vidange(10), Revision(100), Article(10) As String
'Dim i As Integer
'Dim chaine As String
'Dim etat As Integer
 
'Call OuvreFichierTXT("PARAM.txt", Nom, Client, Modele, Aspect, Pupille, PConnecteur, CConnecteur, Hydraulique, Abouts, Vis, Vidange, Revision, Article)
'ComboBox11.Clear
'ComboBox11.AddItem ""
'etat = 0
'   Select Case ComboBox2.Value
'   Case "F2"
'       For i = 0 To UBound(Article)
'        chaine = Right(Article(i), 2)
'        If chaine <> "AE" Or chaine <> "AF" Then etat = 1 Else etat = 0
'        If etat = 1 And Article(i) <> "" Then
'        ComboBox11.AddItem Article(i)
'        End If
'        Next i
'   Case "IT"
'       For i = 0 To UBound(Article)
'        chaine = Right(Article(i), 2)
'        If chaine = "AE" Or chaine = "AF" Then etat = 1 Else etat = 0
'        If etat = 0 And Article(i) <> "" Then
'        ComboBox11.AddItem Article(i)
'        End If
'        Next i
'   Case ""
'       For i = 0 To UBound(Modele)
'       If Modele(i) <> "" Then
'      ComboBox11.AddItem Modele(i)
'     End If
'     Next i
' End Select
' ComboBox11.ListIndex = 1
'   If ComboBox2.ListIndex = 0 Then ComboBox11.ListIndex = 0
'End Sub
 
Private Sub ComboBox11_Enter()
Dim Nom(2, 50) As String 
Dim Client(50), Modele(10), Aspect(10), Pupille(10), PConnecteur(10), CConnecteur(10), Hydraulique(10), Abouts(10), Vis(10), Vidange(10), Revision(100), Article(10) As String
Dim i As Integer
Dim chaine As String
Dim etat As Integer
 
Call OuvreFichierTXT("PARAM.txt", Nom, Client, Modele, Aspect, Pupille, PConnecteur, CConnecteur, Hydraulique, Abouts, Vis, Vidange, Revision, Article)
ComboBox11.Clear
ComboBox11.AddItem ""
etat = 0
   Select Case ComboBox2.Value
    Case "F2"
        For i = 0 To UBound(Article)
        chaine = Right(Article(i), 2)
       If chaine = "AE" Or chaine = "AF" Then etat = 1 Else etat = 0
        If etat <> 1 And Article(i) <> "" Then
       ComboBox11.AddItem Article(i)
       End If
        Next i
    Case "IT"
        For i = 0 To UBound(Article)
        chaine = Right(Article(i), 2)
        If chaine = "AE" Or chaine = "AF" Then etat = 0 Else etat = 1
       If etat <> 1 And Article(i) <> "" Then
        ComboBox11.AddItem Article(i)
        End If
        Next i
    Case ""
        For i = 0 To UBound(Article)
        If Article(i) <> "" Then
        ComboBox11.AddItem Article(i)
        End If
        Next i
    End Select
 
End Sub
 
Private Sub ComboBox11_change()
Dim Nom(2, 50) As String 
Dim Client(50), Modele(10), Aspect(10), Pupille(10), PConnecteur(10), CConnecteur(10), Hydraulique(10), Abouts(10), Vis(10), Vidange(10), Revision(100), Article(10) As String
Dim i As Integer
Dim chaine As String
Dim etat As Integer
 
Call OuvreFichierTXT("PARAM.txt", Nom, Client, Modele, Aspect, Pupille, PConnecteur, CConnecteur, Hydraulique, Abouts, Vis, Vidange, Revision, Article)
'ComboBox2.Clear
'ComboBox2.AddItem ""
chaine = Right(ComboBox11.Value, 2)
If chaine = "AE" Or chaine = "AF" Then etat = 1 Else etat = 0
 
 Select Case etat
 
    Case 0
         ComboBox2.ListIndex = 1
 
    Case 1
        ComboBox2.ListIndex = 2
 
   End Select
 If ComboBox11.ListIndex = 0 Then ComboBox2.ListIndex = 0
 
 
End Sub

Bonjour,

En supposant que le texte d'une combobox est suffisant pour savoir quelle liste fixer dans l'autre combobox, il est possible de simplifier les procédure _Change en utilisant des collections :

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
Option Explicit
 
Private CollCB1 As Collection
Private CollCB2 As Collection
 
'Initialisation de toutes les listes dont nous aurons besoin par la suite (de façon arbitraire pour l'exemple)
Private Sub UserForm_Initialize()
    Dim i As Integer
    Set CollCB1 = New Collection
    Set CollCB2 = New Collection
    For i = 1 To 7
        Me.ComboBox1.AddItem "Item CB1." & i
        Me.ComboBox2.AddItem "Item CB2." & i
        CollCB1.Add Array("Item CB2." & (13 * i Mod 7) + 1, _
                          "Item CB2." & (17 * i Mod 7) + 1, _
                          "Item CB2." & (19 * i Mod 5) + 1), "Item CB1." & i
        CollCB2.Add Array("Item CB1." & (21 * i Mod 7) + 1, _
                          "Item CB1." & (23 * i Mod 7) + 1, _
                          "Item CB1." & (29 * i Mod 5) + 1), "Item CB2." & i
    Next i
End Sub
 
Private Sub ComboBox1_Change()
    If Me.ComboBox1.ListIndex <> -1 Then
        Me.ComboBox2.Clear
        Me.ComboBox2.List = CollCB1(Me.ComboBox1.Text)
    End If
End Sub
 
Private Sub ComboBox2_Change()
    If Me.ComboBox2.ListIndex <> -1 Then
        Me.ComboBox1.Clear
        Me.ComboBox1.List = CollCB2(Me.ComboBox2.Text)
    End If
End Sub

Bonjour,

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
Dim bRefresh As Boolean
 
Private Sub ComboBox1_Change()
If bRefresh = True Then Exit Sub
 RefracheLst Me.ComboBox2, "Client", "[Nom]='" & Me.ComboBox1 & "'"
End Sub
 
 
Private Sub ComboBox2_Change()
If bRefresh = True Then Exit Sub
RefracheLst Me.ComboBox1, "Nom", "[Client]='" & Me.ComboBox2 & "'"
End Sub
 
 
Private Sub UserForm_Initialize()
ShemaIni
RefracheLst Me.ComboBox1, "Nom", "[Client]  is not null"
RefracheLst Me.ComboBox2, "Client", "[Nom]  is not null"
End Sub
Sub RefracheLst(ByRef Lst As Object, Champ As String, Where As String)
bRefresh = True
Lst.Clear
With CreateObject("ADODB.Connection"): .Open ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\MyRepertoire\;Extended Properties=""Text;HDR=no;FMT=Fixed;"";")
       Lst.List = Application.Transpose(.Execute("select [" & Champ & "] from [Testes2Rd#csv] where " & Where).getrows)
       .Close
End With
bRefresh = False
End Sub
Sub ShemaIni()
a = Array("Nom", "Client", "Modele", "Aspect", "Pupille", "PConnecteur", "CConnecteur", "Hydraulique", "Abouts", "Vis", "Vidange", "Revision", "Article")
l = Array(10, 10, 10, 10, 10, 10, 10, 10, 10, 100, 10, 100, 100)
t = ""
t = "[Testes2Rd.csv]" & vbCrLf
t = t & "ColNameHeader = False" & vbCrLf
t = t & "CharacterSet = 1252" & vbCrLf
t = t & "Format = FixedLength" & vbCrLf
 t = "[Testes2Rd.csv]" & vbCrLf
t = t & "ColNameHeader = False" & vbCrLf
t = t & "CharacterSet = 1252" & vbCrLf
t = t & "Format = FixedLength" & vbCrLf
  For i = 0 To UBound(a)
        t = t & "Col" & (i + 1) & "=" & a(i) & " Text Width " & l(i) & vbCrLf
 Next
'Col1=CustomerNumber Text Width 10
 
 
With CreateObject("Scripting.FileSystemObject")
    With .OpenTextFile("C:\MyRepertoire\schema.ini", 2, True)
        .Write t
        .Close
    End With
End With
End Sub

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
Sub test()
With CreateObject("Scripting.FileSystemObject")
If Not .FolderExists("C:\MyRepertoire") Then .CreateFolder "C:\MyRepertoire"
End With
 
 
a = Array("Nom", "Client", "Modele", "Aspect", "Pupille", "PConnecteur", "CConnecteur", "Hydraulique", "Abouts", "Vis", "Vidange", "Revision", "Article")
l = Array(10, 10, 10, 10, 10, 10, 10, 10, 10, 100, 10, 100, 100)
t = ""
For R = 1 To 10
    For I = 0 To UBound(a)
        t = t & "" & Left(R & "_" & I & Space(l(I)), l(I))
    Next
    t = t & vbCrLf
Next
With CreateObject("Scripting.FileSystemObject")
    With .OpenTextFile("C:\MyRepertoire\Testes2Rd.csv", 2, True)
        .Write t
        .Close
    End With
End With
t = "[Testes2Rd.csv]" & vbCrLf
t = t & "ColNameHeader = False" & vbCrLf
t = t & "CharacterSet = 1252" & vbCrLf
t = t & "Format = FixedLength" & vbCrLf
  For I = 0 To UBound(a)
        t = t & "Col" & (I + 1) & "=" & a(I) & " Text Width " & l(I) & vbCrLf
 Next
'Col1=CustomerNumber Text Width 10
 
 
With CreateObject("Scripting.FileSystemObject")
If Not .FolderExists("C:\MyRepertoire") Then .CreateFolder "C:\MyRepertoire"
    With .OpenTextFile("C:\MyRepertoire\schema.ini", 2, True)
        .Write t
        .Close
    End With
End With
 
 
 
 
End Sub