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

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

http://www.exceltip.com/st/Create_Us...Excel/629.html



"Amy Brooks" wrote in message
...
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

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
on the other. Any ideas?

"JLGWhiz" wrote:

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

http://www.exceltip.com/st/Create_Us...Excel/629.html



"Amy Brooks" wrote in message
...
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

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" wrote in message
...
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

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" wrote:

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" wrote in message
...
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

do you want to send me your workbook?


"Amy Brooks" wrote in message
...
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" wrote:

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" wrote in message
...
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

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

i added a userform workbook to the demo page ..

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

this has the code I described earlier

"Patrick Molloy" wrote in message
...
do you want to send me your workbook?


"Amy Brooks" wrote in message
...
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" wrote:

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" wrote in message
...
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

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" wrote:

i added a userform workbook to the demo page ..

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

this has the code I described earlier

"Patrick Molloy" wrote in message
...
do you want to send me your workbook?


"Amy Brooks" wrote in message
...
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" wrote:

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" wrote in message
...
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

did you try my demo file?

"Amy Brooks" wrote in message
...
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" wrote:

i added a userform workbook to the demo page ..

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

this has the code I described earlier

"Patrick Molloy" wrote in message
...
do you want to send me your workbook?


"Amy Brooks" wrote in message
...
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" wrote:

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" wrote in message
...
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

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

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" wrote:

did you try my demo file?

"Amy Brooks" wrote in message
...
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" wrote:

i added a userform workbook to the demo page ..

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

this has the code I described earlier

"Patrick Molloy" wrote in message
...
do you want to send me your workbook?


"Amy Brooks" wrote in message
...
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" wrote:

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" wrote in message
...
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

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 :)


"Amy Brooks" wrote in message
...
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" wrote:

did you try my demo file?

"Amy Brooks" wrote in message
...
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" wrote:

i added a userform workbook to the demo page ..

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

this has the code I described earlier

"Patrick Molloy" wrote in message
...
do you want to send me your workbook?


"Amy Brooks" wrote in message
...
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" wrote:

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" wrote in
message
...
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

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

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


"Patrick Molloy" wrote:

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" wrote in message
...
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