Solved: Disable options in a formfield combobox?

[clhare]

Hello,

I have a macro user form that will have 3 combo boxes in it:

cboPlanName
cboElection1
cboElection2There are five possible plan names in the cboPlanName box. Three of the plan names only have one election, but one of the plan names has two possible elections and the other plan name has four possible elections:

Plan Name 1:

First election will always be Election A
There is never a second electionPlan Name 2:

First election will always be Election B
There is never a second electionPlan Name 3:

First election will always be Election C
There is never a second electionPlan Name 4:

First election can be either:
-- Election D
-- Election E
Second election can be either:
-- Election D
-- Election E
-- No second electionPlan Name 5:

First election can be either:
-- Election F
-- Election G
-- Election H
-- Election I
Second election can be either:
-- Election F
-- Election G
-- Election H
-- Election I
-- No second electionIs there a way to populate the cboElection1 and cboElection2 combo boxes based on what's selected in the cboPlanName combo box, so only the elections that apply to the plan name selected are available?

In the case of Plans 4 and 5, whatever is selected for the first election cannot be selected for the second election.

Your help is greatly appreciated!

[fumei]

Yes. While a little complex, it IS very simple logic. You have in fact done all the work. I wasn't going to actually write for you...but it didn't take all that long. i never opened your attachment. i simply created a userform with the three comboboxes, and dumped in the logic. Here is the code, and the file is attached.Option Explicit
' on initializing the form, populate
' cboPlanName. The others are blank
Private Sub UserForm_Initialize()
cboPlanName.AddItem "Select a Plan Name"
cboPlanName.AddItem "Plan Name 1"
cboPlanName.AddItem "Plan Name 2"
cboPlanName.AddItem "Plan Name 3"
cboPlanName.AddItem "Plan Name 4"
cboPlanName.AddItem "Plan Name 5"
cboPlanName.ListIndex = 0
End Sub

Private Sub cboPlanName_Change()
' when cboPlanname is changed, do the logic
' on the value of cboPlanName
Select Case cboPlanName.ListIndex
Case 0 ' nothing changed
cboPlanName.SetFocus
Case 1 ' Plan 1
cboElection1.Clear
cboElection1.AddItem "Election A"
cboElection1.ListIndex = 0
cboElection2.Clear
cboElection2.AddItem "No second election"
cboElection2.ListIndex = 0
cboElection1.SetFocus
Case 2 ' Plan 2
cboElection1.Clear
cboElection1.AddItem "Election B"
cboElection1.ListIndex = 0
cboElection2.Clear
cboElection2.AddItem "No second election"
cboElection2.ListIndex = 0
cboElection1.SetFocus
Case 3 ' Plan 3
cboElection1.Clear
cboElection1.AddItem "Election C"
cboElection1.ListIndex = 0
cboElection2.Clear
cboElection2.AddItem "No second election"
cboElection2.ListIndex = 0
cboElection1.SetFocus
Case 4 ' Plan 4
cboElection1.Clear
cboElection1.AddItem "Select an Election"
cboElection1.AddItem "Election D"
cboElection1.AddItem "Election E"
cboElection1.ListIndex = 0

cboElection2.Clear
cboElection2.AddItem "Select an Election"
cboElection2.AddItem "Election D"
cboElection2.AddItem "Election E"
cboElection2.AddItem "No second election"
cboElection2.ListIndex = 0
cboElection1.SetFocus
Case 5
cboElection1.Clear
cboElection1.AddItem "Select an Election"
cboElection1.AddItem "Election F"
cboElection1.AddItem "Election G"
cboElection1.AddItem "Election H"
cboElection1.AddItem "Election I"
cboElection1.ListIndex = 0

cboElection2.Clear
cboElection2.AddItem "Select an Election"
cboElection2.AddItem "Election F"
cboElection2.AddItem "Election G"
cboElection2.AddItem "Election H"
cboElection2.AddItem "Election I"
cboElection2.ListIndex = 0
cboElection1.SetFocus
End Select
End Sub

Private Sub CommandButton1_Click()
Unload Me
End SubThe reason you need to always .Clear is otherwise the combobox will keep filling up!

I must point out that in the first three logic statements, the result should not really be going into a combobox. Comboboxes are for multiple choices. There is no choice. Properly done, you would make the Election comboboxes disappear, and the result (Election A, or B, or C) appear in a label. If PlanName is 4, or 5, you would make the label disappear, and the Election comboboxes appear.

[fumei]

Ooooops
In the case of Plans 4 and 5, whatever is selected for the first election cannot be selected for the second election.Forgot to put that logic in. Hopefully you can figure out how to do that.

Use the _Change event for cboElection1. Do a Select case on the value.

[clhare]

This is awesome and exactly what I needed. Thank you so much. I was able to figure out how to do that last part using your code as a guide.

:bow:

[fumei]

You are welcome.