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
|
Option Explicit
Dim Lig As Long
Dim niv As Integer
Private Sub UserForm_Initialize()
Dim t As Object
Dim c As Range
Set t = CreateObject("Scripting.Dictionary")
'Remplissage de la ComboBox1 sans doublons
With Sheets("Base")
For Each c In .Range("A2", .Cells(Rows.Count, 1).End(xlUp))
t.Item(c.Value) = c.Value
Next c
End With
Me.ComboBox1.List = t.items
Set t = Nothing
'Définition de la ComboBox2 à 2 colonnes
With Me.ComboBox2
.ColumnCount = 2
.ColumnWidths = .Width - 2 & ";0"
End With
End Sub
Private Sub ComboBox1_Change()
Dim c As Range
Application.ScreenUpdating = False
Me.ComboBox2.Clear
'filtrage de la feuille de donnée sur la vaaleur de combo et remplissage
'de combo2 par les valeurs des cellules filtées et le numéro de ligne
With Sheets("Base")
.Range("A1", .Cells(Rows.Count, 1).End(xlUp)).AutoFilter field:=1, Criteria1:=Me.ComboBox1.Value
For Each c In .Range("B2:B" & .Cells(Rows.Count, 1).End(xlUp).Row).SpecialCells(xlCellTypeVisible)
With Me.ComboBox2
.AddItem c.Value
.List(.ListCount - 1, 1) = c.Row
End With
Next c
.Range("A1", .Cells(Rows.Count, 1).End(xlUp)).AutoFilter
End With
End Sub |
Partager