Make a combo box dependant on another

[Amy Brooks]

Hi,

I have two combo boxes, one named Division, the other named Sub-Division.
I want to make it so that if you select (for example) Ambulance from the
Division list, the Sub-Division list will only show related items, like NHS,
Private, Air. The lists are as follows:

Ambulance
Air
NHS
Private
Voluntary
Vehicle Builder
Export
Distributor
End User
Group
Hospital
NHS
Private
Distributor
Industry
N/A
Mortuary
Distributor
Funeral Director

The code I have so far is:
------------------------------------------------------------------------------------------------
Sub Item_CustomPropertyChange(ByVal Name)
Select Case Name
Case "cboDivision"
Call SetSubDivision
End Select
End Sub
_____________________________________________

Sub SetSubDivision
Set objInsp = Item.GetInspector
Set objPage = objInsp.ModifiedFormPages("General")
Set Division = objPage.Controls("cboSubDivision")
Select Case Item.UserProperties ("cboDivision")
Case "Ambulance"
cboSubDivision.List = Split("Air,NHS,Private,Vehicle Builder,Voluntary",",")
Case "Export"
cboSubDivision.List = Split("Distributor,End User,Ferno Group",",")
Case "Hospital"
cboSubDivision.List = Split("Distributor,MOD,NHS,Private",",")
Case "Industry"
cboSubDivision.List = Split("N/A",",")
Case "Mortuary"
cboSubDivision.List = Split("Distributor,Funeral Director",",")
End Select
End Sub
------------------------------------------------------------------------------------------------

I'm not sure if this is correct, or where to put it.
Help would be appreciated!

Thanks,
Amy

 

[Sue Mosher [MVP]]

Code behind an Outlook form is placed in the code window displayed by the
View Code command in design mode.

cboDivision sounds like the name of a control, not a custom property. See
http://www.outlookcode.com/article.aspx?ID=38 for an example of the correct
usage of CustomProperty Change and how to refer to properties of a control.

 

[Amy Brooks]

Hi Sue,

It was actually your solution I found online, and I tried to modify it to
suit my needs.
I've had a look at the site you posted, but I'm finding it a bit difficult
to understand.
If I go to the Value tab of Properties (as shown on the website), I have
actually used the name: Division. I used cboDivision when you go into Display
tab.

 

[Sue Mosher [MVP]]

That suggests that Division is the name of the *property* and cboDivision is
the name of the *control*. Only the property value is relevant in this
scenario. "Division" would be the string value needed in your Case statement
in the Item_CustomPropertyChange handler and Item.UserProperties
("Division") would be the value in the later Select Case statement.

You should also change this statement:

Set Division = objPage.Controls("cboSubDivision")

to

Set cboSubDivision = objPage.Controls("cboSubDivision")

so that there is a cboSubDivision object variable for all the subsequen
cboSubDivision.List expressions.
--
Sue Mosher, Outlook MVP
Author of Microsoft Outlook 2007 Programming:
Jumpstart for Power Users and Administrators
http://www.outlookcode.com/article.aspx?id=54

 

[Amy Brooks]

You should also change this statement:

Set Division = objPage.Controls("cboSubDivision")

*Slaps forehead* I can't believe I did that! Well spotted mistake

I think I understand what you mean now, so property name is like the field
name, which you see in the field chooser, and control name is basically the
name of the text box / combo box etc?

I've changed my script, and now works perfectly Thanks!

 

[Sue Mosher [MVP]]

Yes, "property" and "field" are synonyms, and the property and the control
that displays it are two completely different entities.

Glad to hear you have it working.