thedon32
07-02-2013, 09:37 AM
Hi,
I am really in need of some help here, I have been given the task of trying to come up with a form in excel that the rest of my company can use. I have managed to get an extract into another tab, which details agents and the departments those agents work for, what I would like to do is have 2 combo boxes, the first one has a list of all the available departments, sales, customer service, complaints etc. The other combo box will have a list of all the agents, here's the tricky bit. If the user selects sales for instance, only the agents who work in sales will be displayed in the second combo box.
I had a search around the internet and found this piece of code:
Private Sub ComboBox1_Change()
Dim strRange As String
If ComboBox1.ListIndex > -1 Then
strRange = ComboBox1
Label2.Caption = strRange
strRange = Replace(strRange, " ", "_")
With ComboBox2
.RowSource = vbNullString
.RowSource = strRange
.ListIndex = 0
End With
Else
Label2.Caption = "Associated Items"
End If
End Sub
Though it seems only to work when you create a user form, as when I try and bring the code into the main workbook, I get a runtime error as it doesn't recognise the '.RowSource' function. Can anyone help me with this as I am :banghead: :banghead:
Cheers
I am really in need of some help here, I have been given the task of trying to come up with a form in excel that the rest of my company can use. I have managed to get an extract into another tab, which details agents and the departments those agents work for, what I would like to do is have 2 combo boxes, the first one has a list of all the available departments, sales, customer service, complaints etc. The other combo box will have a list of all the agents, here's the tricky bit. If the user selects sales for instance, only the agents who work in sales will be displayed in the second combo box.
I had a search around the internet and found this piece of code:
Private Sub ComboBox1_Change()
Dim strRange As String
If ComboBox1.ListIndex > -1 Then
strRange = ComboBox1
Label2.Caption = strRange
strRange = Replace(strRange, " ", "_")
With ComboBox2
.RowSource = vbNullString
.RowSource = strRange
.ListIndex = 0
End With
Else
Label2.Caption = "Associated Items"
End If
End Sub
Though it seems only to work when you create a user form, as when I try and bring the code into the main workbook, I get a runtime error as it doesn't recognise the '.RowSource' function. Can anyone help me with this as I am :banghead: :banghead:
Cheers