Linking Combo boxes

[thedon32]

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

Cheers

[snb]

I'd say: start at the beginning of VBA; study a handbook, master VBA from simple to complex and do not use (copy) code you do not understand fully.

[thedon32]

I understand what the code is doing, I don't understand why it only works in a user form, or really why RowSource is only recognised in a user form and not in the main spreadsheet.

[snb]

Because an ActiveX-control <> Userformcontrol