I don't know where you put your sub cboEmployee_List and how
Item.UserProperties("cboDepartment") can change cboEmployee's list. I
think Excel doesn't have such a Property like UserProperties.
Sub CboDepartment_Exit(ByVal Cancel As MSForms.ReturnBoolean) is a event
Macro that is fired when you exit the CboDepartment's ComboBox. Cancel
is something like a flag and can be used for validating your selected
value. If you set it true, you can't exit the ComboBox. One example of
using Cancel is something like below. You can't exit ComboBox when you
select a value other than "Accounts", "Sales" and "Marketing" by
inputting a value directly into the ComboBox.

Private Sub CboDepartment_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim arr
Select Case Me.CboDepartment.Value
Case "Accounts"
Cboemployee.List = Split("C Dawson,,J Cooper,L Bottomley", ",")
Case "Sales"
Cboemployee.List = Split("A Soar,B Miller,D Padgett,P North", ",")
Case "Marketing"
Cboemployee.List = Split("A Brock,V Woodford,A Brooks", ",")
End Select
arr = Array("Accounts", "Sales", "Marketing")
If IsError(Application.Match(Me.CboDepartment.Value, arr, 0)) Then
Cancel = True
Else
Cancel = False
End If
End Sub

Instead of using CboDepartment_Exit, you can do almost same thing using
Change event. this is fired when you selected different value.

Private Sub CboDepartment_Change()
Select Case Me.CboDepartment.Value
Case "Accounts"
Cboemployee.List = Split("C Dawson,,J Cooper,L Bottomley", ",")
Case "Sales"
Cboemployee.List = Split("A Soar,B Miller,D Padgett,P North", ",")
Case "Marketing"
Cboemployee.List = Split("A Brock,V Woodford,A Brooks", ",")
End Select
End Sub

Keiji

Amy Brooks wrote:
Hi Keiji

This works a treat! Thanks :D

Now, your solution was different to mine with the following lines:

Mine:
Sub cboEmployee_List()
Select Case Item.UserProperties("cboDepartment")

Yours:
Private Sub CboDepartment_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Select Case Me.CboDepartment.Value


I can see why the second line works better, but I don't understand the
change in the first line. What does the bit in brackets mean?


Thanks again for the answer :D



"keiji kounoike" <"kounoike AT mbh.nifty." wrote:

How about putting the code below into your Userform module.

Private Sub CboDepartment_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Select Case Me.CboDepartment.Value
Case "Accounts"
CboEmployee.List = Split("C Dawson,,J Cooper,L Bottomley", ",")
Case "Sales"
CboEmployee.List = Split("A Soar,B Miller,D Padgett,P North", ",")
Case "Marketing"
CboEmployee.List = Split("A Brock,V Woodford,A Brooks", ",")
End Select
End Sub

Keiji

Amy Brooks wrote:
Hi,

I've tried to modify the following code (originally intended for an Outlook
form) to work in an Excel form. As I expected, it doesn't work, but I'm not
entirely sure why. My guess is that either, I've put it in the wrong place,
the sub name is wrong, or the code itself has a problem. The code is supposed
to make one combo box (Employee) dependant on another (Department). For
example, if I selected the Marketing department from the Department combo
box, I would only get the options A Brock, A Brooks, and V Woodford.

What should I use as the Sub name?
Where is the code supposed to go?
Are there any other mistakes in the code that could be causing it not to work?

__________________________________________________ __________________
Sub cboEmployee_List()
Select Case Item.UserProperties("cboDepartment")
Case "Accounts"
cboEmployee.List = Split("C Dawson,,J Cooper,L Bottomley", ",")
Case "Sales"
cboEmployee.List = Split("A Soar,B Miller,D Padgett,P North",
",")
Case "Marketing"
cboEmployee.List = Split("A Brock,V Woodford,A Brooks", ",")
End Select
End Sub
__________________________________________________ __________________

Thanks!
Amy