Dependant combo box in Excel form

[Amy Brooks]

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

 

[JLGWhiz]

Take a look at this and see if it will help you to organize your code.

http://www.exceltip.com/st/Create_User_Forms_in_Microsoft_Excel/629.html

 

[Amy Brooks]

The article doesn't seem to mention how to actually make a combo box
dependant on another. However, it did show me how to add items using code.
Using that, I have got the Department combo box populated, and I can populate
the Employee combo box using the same method, but I can't make one dependant

 

[Patrick Molloy]

I have a demo book for indirect validation
http://www.xl-expert.com/IndirectValidation.htm
this is using validation in cells

Are you using a userform?
if you are, then the code should go in the department listbox change event
If you've dropped these onto a sheet, then you'll code the objects on the
sheet's code page (right click the sheet tab & select View Code)

If you have the combos on a sheet...
range name "Department" - table of departments
range name "emp.Sales" - table of employees in sales
range name "emp.Marketing"
range name "emp.Accounts"

combobox cboDepartments : ListFillRange: Departments
sheet code page:
Option Explicit
Private Sub cboDepartment_Change()
With cboEmployee
.ListFillRange = "emp." & cboDepartment
.Text = "<choose>"
End With
End Sub


please see the Filtered Combobox on the weblink - its this example

 

[Amy Brooks]

This doesn't seem to be working for me, maybe because I'm using a user form??
I viewed the form code and added the code there, but when I select a
department, the following message appears:
____________________________________________________________________
Compile error:

Method or data member not found
____________________________________________________________________

.ListFillRange = "emp." & cboDepartment

and highlights the cboDepartment in the above string.

Any ideas why it's doing this? Oh, and it won't let me add the Option
Explicit to the beginning, otherwise it goes to the end of the previous Sub.
I don't know if this would affect it's functionality.

 

[Patrick Molloy]

do you want to send me your workbook?

This doesn't seem to be working for me, maybe because I'm using a user
form??
I viewed the form code and added the code there, but when I select a
department, the following message appears:
____________________________________________________________________
Compile error:

Method or data member not found
____________________________________________________________________

.ListFillRange = "emp." & cboDepartment

and highlights the cboDepartment in the above string.

Any ideas why it's doing this? Oh, and it won't let me add the Option
Explicit to the beginning, otherwise it goes to the end of the previous
Sub.
I don't know if this would affect it's functionality.

 

[keiji kounoike]

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

 

[Patrick Molloy]

i added a userform workbook to the demo page ..

http://www.xl-expert.com/IndirectValidation.htm

this has the code I described earlier

 

[Amy Brooks]

I tried your example, but when I select a department, it throws up the
following message:
_____________________________________________________________________
Run-time error '1004':

Method 'Worksheets' of object '_Global' failed
_____________________________________________________________________

which points to the following line:

For Each cell In Worksheets("Datatables").Range("emp." & dept).Cells

Not sure what's wrong there

 

[Patrick Molloy]

did you try my demo file?

I tried your example, but when I select a department, it throws up the
following message:
_____________________________________________________________________
Run-time error '1004':

Method 'Worksheets' of object '_Global' failed
_____________________________________________________________________

which points to the following line:

For Each cell In Worksheets("Datatables").Range("emp." & dept).Cells

Not sure what's wrong there

 

[Amy Brooks]

Hi Keiji

This works a treat! Thanks

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

 

[Amy Brooks]

Yes, but when I tried to select a department from your form, it gave that
error message.

The post below has actually answered my question anyway, but you've been a
great help, and thanks for trying a few examples with me, and making those
demos

Thanks!

 

[Patrick Molloy]

i can't see any issues with the file and it runs in both 2003 and 2007 ok

the issue is that the list is hard-codes, so becomes less manageable
My solution was to use named ranges, which makes the code more flexible in
that the data is easier to manage.

whatever, Keiji kounoike has given a fix

 

[keiji kounoike]

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

 

[jatman]

this will work great for me, but one question. how do i put the selection
into a cell. ex: i select an option from the list of employees and i want
to carry that value forwad for something else.

what i have a list of values that will be assigned to the cell next to the
employee name. i select an employee and that value is used in a formula.

i may or may not get this reply because i have found that my posts and
replies are getting jacked onto other sites such as egghead and stuff so i
may not find your reply!

jat