Private Sub ComboBox1_Change()
Dim O As Worksheet
If Me.ComboBox1.Value = "" Then Exit Sub
For Each O In Sheets
Select Case O.Name
Case "ARMOIRES"
RemplirListbox ListBox1, O.Name, 6
Case "POINTS LUMINEUX"
RemplirListbox ListBox2, O.Name, 5
Case "LANTERNES"
RemplirListbox ListBox3, O.Name, 7
End Select
Next O
End Sub
Private Sub RemplirListbox(LisB As MSForms.ListBox, Nomfe As String, NBcol As Byte)
Dim col As String, i As Long, cellule As Range, LigDep As Integer
col = "a"
LigDep = 2
'
With LisB
.Clear
.ColumnCount = NBcol + 1
Select Case NBcol
Case 5
.ColumnWidths = "60;60;60;60;60;0"
Case 6
.ColumnWidths = "60;60;60;60;60;60;0"
Case 7
.ColumnWidths = "60;60;60;60;60;60;60;0"
End Select
'.Style = fmStyleDropDownList '
For Each cellule In Sheets(Nomfe).Range(col & LigDep & ":" & col & Sheets(Nomfe).Range(col & Columns(1).Cells.Count).End(xlUp).Row)
If CStr(cellule.Value) = Me.ComboBox1.Value Then
.AddItem cellule.Value
For i = 1 To NBcol
.List(.ListCount - 1, i) = cellule.Offset(0, i).Value
Next i
.List(.ListCount - 1, .ColumnCount - 1) = cellule.Row' numero de ligne si nécessaire
End If
Next cellule
End With
End Sub