Option Explicit
Private Sub Userform_initialize()
With Sheets("BD") 'prend en compte l'onglet "BD"
'alimentation dynamique des comboboxes
ComboBox2.List = .Range("A2:A" & .Cells(Application.Rows.Count, 1).End(xlUp).Row).Value
ComboBox1.List = .Range("A2:A" & .Cells(Application.Rows.Count, 1).End(xlUp).Row).Value
ComboBox3.List = .Range("B2:B" & .Cells(Application.Rows.Count, 2).End(xlUp).Row).Value
ComboBox4.List = .Range("C2:C" & .Cells(Application.Rows.Count, 3).End(xlUp).Row).Value
ComboBox5.List = .Range("D2:D" & .Cells(Application.Rows.Count, 4).End(xlUp).Row).Value
ComboBox6.List = .Range("F2:F" & .Cells(Application.Rows.Count, 6).End(xlUp).Row).Value
End With 'fin de la prise en compte de l'onglet "BD"
End Sub
Private Sub ComboBox6_Change()
Dim pl As Range 'déclare la variable pl (PLage)
Dim col As Byte 'déclare la variable col (COLonne)
Me.ComboBox7.Clear 'vide la ComboBox7
With Sheets("BD") 'prend en compte l'onglet "BD"
Set pl = .Range("G1:" & .Cells(1, Application.Columns.Count).End(xlToLeft).Address(0, 0)) 'définit dynamiquemment la plage pl
If Me.ComboBox6.Value <> "" Then col = pl.Find(CStr(Me.ComboBox6.ListIndex), , xlValues, xlWhole).Column 'si la ComboBox6 est renseignée, récupère le numéro de colonne correspondant
Me.ComboBox7.List = .Range(.Cells(2, col), .Cells(Application.Rows.Count, col).End(xlUp)).Value 'alimente dynamiquement la ComboBox7
End With 'fin de la prise en compte de l'onglet "BD"
End Sub