PDA

View Full Version : [SOLVED] filling combobox's issue



LearningEXL
04-10-2015, 02:50 AM
i have a sheet called "Agency_Contacts" in col B is a list of agencies which can be duplicate and i col C is a list of contracts that are related to B.

so there maybe 5 contract related to 1 agency or more.

what im trying to do is populate combobox 1 (Cbo_Agency) and then have Combobox 2 (Cbo_ACon) fill with all the contracts that is related to combobox 1

i have tried the following code but it does not populate the combobox's


Private Sub Cbo_Agency_Change()

myVal = Me.Cbo_Agency.Value


'loop thru col B
'lr = ThisWorkbook.Sheets("Sheet6").Cells(Rows.Count, 8).End(xlUp).Row


'clear combobox 2
Me.Cbo_ACon.Clear


'loop thru
For x = 9 To lr
If myVal = ThisWorkbook.Sheets("Sheet6").Cells(x, 2) Then
'add to combobox
Me.Cbo_ACon.AddItem ThisWorkbook.Sheets("Sheet6").Cells(x, "C")
End If
Next x



End Sub

i would really be greatfull if someone could help me please

LearningEXL
04-10-2015, 03:22 AM
***Solved***

Thank you

LearningEXL
04-10-2015, 03:23 AM
Private Sub Cbo_Agency_Change()

myVal = Me.Cbo_Agency.Value


'loop thru col B
'lr = ThisWorkbook.Sheets("Sheet6").Cells(Rows.Count, 8).End(xlUp).Row


'clear combobox 2
Me.Cbo_ACon.Clear


With ThisWorkbook.Worksheets("Agency_Contract")
lr = .Cells(.Rows.Count, 2).End(xlUp).Row
End With


'loop thru
For x = 9 To lr
If myVal = ThisWorkbook.Sheets("Agency_Contract").Cells(x, 2) Then
'add to combobox
Me.Cbo_ACon.AddItem ThisWorkbook.Sheets("Agency_Contract").Cells(x, "C")
End If
Next x


End Sub