Definir l'index d'une combobox comme une variable
Bonjour a tous,
Suite a la mise en place d'un userform avec plusieurs combobox, je souhaite utiliser une boucle sur les index de la combobox pour optimiser mon code.
Toutefois, je ne sais pas comment definir la variable suivante :
Dim Obj as ?
Obj = ("ComboBox" & CbxIndex)
Comment definir le tout pour que le code marche properment?
Merci d'avance
Code:
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
| Sub Control_delivery_report()
''''''''''''''''''''''''''
'''This macro is developped to monitor the delivery from RPC to SANG VILLA SITE
Dim Ws As Worksheet
Dim pl, r As Range
Dim Obj as ?
Dim pa As String
Dim CbxIndex, N_cbxIndex As Integer
''''''''''''''''''''''''''
'''Collect the data form userform Delivery Report
Set Ws = Worksheets("Database_production")
Set Obj = "ComboBox" & CbxIndex
Set Obj1 = "Combobox" & N_cbxIndex
''''''''''''''''''''''''''
'''Common data to the all userform
Delivery_date = UserForm6.Calendar1.Value
subcontractor = UserForm6.ComboBox1.Value
truck_nb = UserForm6.ComboBox2.Value
''''''''''''''''''''''''''
'''Specific data from userfrom
Ws.Select
If CbxIndex = 3 Or CbxIndex = 5 Or CbxIndex = 7 Or CbxIndex = 9 Or CbxIndex = 11 Then
N_cbxIndex = CbxIndex + 1
nblines = WorksheetFunction.CountA(Range("A:A"))
Set pl = Ws.Range("D2:D" & nblines)
Set r = pl.Find(CStr(UserForm6.Obj.Value & UserForm6.Obj1.Value))
If Not r Is Nothing Then
pa = r.Address
sr1 = Cells(Range(pa).Row, 1)
End If
Sheets("Database").Select
Cells(sr1, 14) = Delivery_date
Cells(sr1, 15) = subcontractor
Cells(sr1, 16) = truck_nb
End If
End Sub |