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