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
| Option Explicit
Private Sub Userform_Initialize()
Dim i As Long, LastLig As Long
Application.ScreenUpdating = False
With Worksheets("BD")
LastLig = .Cells(.Rows.Count, "A").End(xlUp).Row
Me.ComboBox1.Clear
For i = 2 To LastLig
If .Range("B" & i) <> "" Then
Me.ComboBox1 = .Range("B" & i)
If Me.ComboBox1.ListIndex = -1 Then Me.ComboBox1.AddItem .Range("B" & i)
End If
Next i
End With
Me.ComboBox1.ListIndex = -1
With Me.ListBox1
.ColumnCount = 12
.ColumnWidths = "30;80;80;80;80;80;80;80;80;80;80;80"
End With
End Sub
Private Sub ComboBox1_Change()
Dim LastLig As Long
Dim Code As String
Dim c As Range
Application.ScreenUpdating = False
Me.ListBox1.Clear
Code = Me.ComboBox1.Value
If Me.ComboBox1.ListIndex > -1 Then
With Worksheets("BD")
.AutoFilterMode = False
LastLig = .Cells(.Rows.Count, "B").End(xlUp).Row
.Range("B1:B" & LastLig).AutoFilter Field:=1, Criteria1:=Code
For Each c In .Range("B2:B" & LastLig).SpecialCells(xlCellTypeVisible)
With Me.ListBox1
.AddItem c
.List(.ListCount - 1, 0) = c.Offset(0, -1)
.List(.ListCount - 1, 1) = c.Offset(0, 0)
.List(.ListCount - 1, 2) = c.Offset(0, 1)
.List(.ListCount - 1, 3) = c.Offset(0, 2)
.List(.ListCount - 1, 4) = c.Offset(0, 3)
.List(.ListCount - 1, 5) = c.Offset(0, 4)
.List(.ListCount - 1, 6) = c.Offset(0, 5)
.List(.ListCount - 1, 7) = c.Offset(0, 6)
.List(.ListCount - 1, 8) = c.Offset(0, 7)
.List(.ListCount - 1, 9) = c.Offset(0, 8)
'.List(.ListCount - 1, 10) = c.Offset(0, 9)
'.List(.ListCount - 1, 11) = c.Offset(0, 11)
End With
Next c
.AutoFilterMode = False
End With
End If
End Sub
Private Sub ListBox1_Click()
Me.TextBox1 = ListBox1.List(ListBox1.ListIndex, 0)
Me.TextBox2 = ListBox1.List(ListBox1.ListIndex, 1)
Me.TextBox3 = ListBox1.List(ListBox1.ListIndex, 2)
Me.TextBox4 = ListBox1.List(ListBox1.ListIndex, 3)
Me.TextBox5 = ListBox1.List(ListBox1.ListIndex, 4)
Me.TextBox6 = ListBox1.List(ListBox1.ListIndex, 5)
Me.TextBox7 = ListBox1.List(ListBox1.ListIndex, 6)
Me.TextBox8 = ListBox1.List(ListBox1.ListIndex, 7)
Me.TextBox9 = ListBox1.List(ListBox1.ListIndex, 8)
Me.TextBox10 = ListBox1.List(ListBox1.ListIndex, 9)
End Sub |
Partager