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" <(E-Mail Removed)> wrote in message
news:99BE1FBC-6AEF-4E96-8859-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:99BE1FBC-6AEF-4E96-8859-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
news:99BE1FBC-6AEF-4E96-8859-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:99BE1FBC-6AEF-4E96-8859-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
news:C20FE267-8AF5-43FB-B7F5-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:99BE1FBC-6AEF-4E96-8859-(E-Mail Removed)...
>> > 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" <(E-Mail Removed)> wrote in message
news92FB080-7D37-4592-8043-(E-Mail Removed)...
> do you want to send me your workbook?
>
>
> "Amy Brooks" <(E-Mail Removed)> wrote in message
> news:C20FE267-8AF5-43FB-B7F5-(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>>> news:99BE1FBC-6AEF-4E96-8859-(E-Mail Removed)...
>>> > 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" <(E-Mail Removed)> wrote in message
> news92FB080-7D37-4592-8043-(E-Mail Removed)...
> > do you want to send me your workbook?
> >
> >
> > "Amy Brooks" <(E-Mail Removed)> wrote in message
> > news:C20FE267-8AF5-43FB-B7F5-(E-Mail Removed)...
> >> 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" <(E-Mail Removed)> wrote in message
> >>> news:99BE1FBC-6AEF-4E96-8859-(E-Mail Removed)...
> >>> > 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" <(E-Mail Removed)> wrote in message
news:9B0A3A98-5E94-416D-BB0A-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news92FB080-7D37-4592-8043-(E-Mail Removed)...
>> > do you want to send me your workbook?
>> >
>> >
>> > "Amy Brooks" <(E-Mail Removed)> wrote in message
>> > news:C20FE267-8AF5-43FB-B7F5-(E-Mail Removed)...
>> >> 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" <(E-Mail Removed)> wrote in message
>> >>> news:99BE1FBC-6AEF-4E96-8859-(E-Mail Removed)...
>> >>> > 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
>> >>>