coding worksheets for undesired selections

[Ed]

I am in the process of building a complex workbook.
Within the workbook is several worksheets. Each worksheet
has several ComboBoxes that allow the user to make a
selection of several options. However, some of the
options do not relate. My task is to code the sheet so
that unrelated choices are not an option or when a
selection is made that does not relate to a previous
selection then a Msgbox will appear and tell the user
what to change. Below is an example of what is included.
multiple ComboBoxes each with a formula string that
includes VLookups from tables within the worksheet.
Example.
ComboBox 1 will include the following options:
8
16
12
32
40
48
ComboBox 2 will include the following:
Stitch
Perfect
Paste

If the user choses any number over 16 in ComboBox 1 and
then chooses "Paste" from ComboBox 2 I need to do 1 of 2
things.
I need either Paste to not be an option if 16 or higher
is chosen OR I need a MsgBox explaining the problem.
Many thanks to anyone who can point me in the right
direction for this or who knows the code I am looking
for.
Ed

 

[Tom Ogilvy]

Private Sub combobox1_Click()
if Combobox1.ListIndex > 2 then
if combobox2.ListFillRange = "" then
Combobox2.RemoveItem 2
end if
end sub

Private Sub Combobox2_click()
if Combobox1.ListIndex >2 and Combobox2.Value = "Paste" then
msgbox "Paste is not a option for you"
Combobox2.ListIndex = -1
end if
End Sub


You can't remove an item if the box is populated using ListfillRange

in the special case where the last item in the list is the only one to be
excluded

What if the user changes their mind and changes the selection in Combobox1?

 

[Ed]

Tom,
Just wanted to thank you for your help. To answer your
question. I was thinking that once the user changed their
mind and changed ComboBox 1 then ComboBox 2 would
repopulate with the option that had been previously left
out.
Thanks again for your help.
Ed

 

[Tom Ogilvy]

Private Sub combobox1_Click()
Combobox2.ListFillRange = ""
if Combobox1.ListIndex > 2 then
Combobox2.List = Array("Stitch","Perfect")
else
Combobox2.List = Array("Stitch","Perfect","Paste")
end if
end sub