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
| Private Sub ComboBox_ville_cp1_Change()
Dim ChoixFiltre As Integer, choix2 As String
Application.ScreenUpdating = False
Dim verif As Boolean
ComboBox_ville_cp2.Clear
With Sheets("Feuil1")
If CheckBox1 Then
ChoixFiltre = 1
.Range("$A$1:$K$1").AutoFilter 'je mets le filtre automatique
.Range("$A$1:$K$" & derlg).AutoFilter Field:=1, Criteria1:=ComboBox_ville_cp1
tb2 = .Range("A2:I" & derlg).SpecialCells(xlCellTypeVisible)
ComboBox_ville_cp2.AddItem tb2(1, 2)
TextBox_dept = tb2(1, 3)
TextBox_Region = tb2(1, 4)
TextBox_Sp = tb2(1, 6)
TextBox_Prefect = tb2(1, 5)
TextBox1 = tb2(1, 7)
TextBox2 = tb2(1, 8)
TextBox3 = tb2(1, 9)
.AutoFilterMode = False
Else
ChoixFiltre = 2
If Len(ComboBox_ville_cp1) = 5 Then
.Range("$A$1:$K$1").AutoFilter
.Range("$A$1:$K$" & derlg).AutoFilter Field:=2, Criteria1:=ComboBox_ville_cp1
tb2 = .Range("A2:K" & derlg).SpecialCells(xlCellTypeVisible)
For x = 1 To UBound(tb2, 1)
ComboBox_ville_cp2.AddItem tb2(x, 1)
Next x
TextBox_dept = tb2(1, 3)
TextBox_Region = tb2(1, 4)
TextBox_Sp = tb2(1, 6)
TextBox_Prefect = tb2(1, 5)
TextBox1 = tb2(1, 7)
TextBox2 = tb2(1, 8)
TextBox3 = tb2(1, 9)
ComboBox_ville_cp2.ListIndex = 0
.AutoFilterMode = False
End If
End If
If Len(ComboBox_ville_cp1) = 5 Then
verif = False
For y = 1 To UBound(tb2, 1)
If Str(tb2(y, 2)) = Str(ComboBox_ville_cp1) Then
verif = True: Exit For
End If
Next y
If verif = False Then
MsgBox "aucune correspondance trouvée"
ComboBox_ville_cp2.Value = "INCONNU"
ComboBox_ville_cp2.Clear
TextBox_dept = ""
TextBox_Region = ""
TextBox_Sp = ""
TextBox_Prefect = ""
End If
End If
.Range("$A$1:$K$" & derlg).AutoFilter Field:=ChoixFiltre, Criteria1:=ComboBox_ville_cp1 'je filtre sur ComboBox_ville_cp1
If .Range("A2:A" & derlg).SpecialCells(xlCellTypeVisible).Rows.Count = 1 Then 'on vérifie que le résultat contient plus d'une ligne
ListBoxVilles.AddItem .Range("A2:A" & derlg).SpecialCells(xlCellTypeVisible) 'sinon, on donne la ligne unique à la listbox
Else
tb2 = .Range("A2:A" & derlg).SpecialCells(xlCellTypeVisible) 'Je mets le résultat dans un tableau (cellules visibles)
ListBoxVilles.list = tb2 'j'ai renommé la listbox "listboxvilles" et y insère les données du tableau
End If
.AutoFilterMode = False 'j'enlève le filtre
End With
'Nbre item dans la listbox
Label6.Caption = "Il y a.." & ListBoxVilles.ListCount & " " & "Ville dans le canton"
Application.ScreenUpdating = True
End Sub |
Partager