I have a userform with a number of combo boxes. I would like to be
able to compare all the values, which the user has selected, with a
table on a spreadsheet. The top axis of the table has all the combobox
names on it, and the vertical axis is just numbers from 1 to N. Each
cell in a row of the table may contain a possible value of the combo
box, whose name is at the top of its column. The cell may also be
blank; this represents a don't-care. Each row then represents a
combination of values which are forbidden on the form.

EG. The table might look like this:

¦Combo1Name ¦Combo2Name ¦Combo3Name ¦Combo4Name
-------------------------------------------------------------------------------------------------------------------------------------
1 ¦Value1 ¦ ¦ValueTuesday ¦ValueMay
2 ¦Value1 ¦Valueb ¦ ¦
3 ¦Value3 ¦ ¦ ¦ValueJuly

Meaning that the values, as represented in rows 1, 2 and 3, cannot be
chosen in combination. If they are, I plan to make a warning appear.
I think I should check this on the change event of the combo boxes,
but I'm just not sure how to go about checking the combinations of
values against the table.
I'd be most grateful for any ideas.

Private Sub ComboBox1_Change()

If Not IsError(Application.Match(ComboBox1.Value, _
Worksheets("Sheet1").Columns(1),0) Then
Msgbox "Found"
Else
MsgBox "Not found"
End If
End Sub

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"mcgurkle" wrote in message
s.com...
I have a userform with a number of combo boxes. I would like to be
able to compare all the values, which the user has selected, with a
table on a spreadsheet. The top axis of the table has all the combobox
names on it, and the vertical axis is just numbers from 1 to N. Each
cell in a row of the table may contain a possible value of the combo
box, whose name is at the top of its column. The cell may also be
blank; this represents a don't-care. Each row then represents a
combination of values which are forbidden on the form.

EG. The table might look like this:

¦Combo1Name ¦Combo2Name ¦Combo3Name ¦Combo4Name
-------------------------------------------------------------------------------------------------------------------------------------
1 ¦Value1 ¦ ¦ValueTuesday ¦ValueMay
2 ¦Value1 ¦Valueb ¦ ¦
3 ¦Value3 ¦ ¦ ¦ValueJuly

Meaning that the values, as represented in rows 1, 2 and 3, cannot be
chosen in combination. If they are, I plan to make a warning appear.
I think I should check this on the change event of the combo boxes,
but I'm just not sure how to go about checking the combinations of
values against the table.
I'd be most grateful for any ideas.

On Jun 11, 1:16 pm, "Bob Phillips" wrote:
Private Sub ComboBox1_Change()

If Not IsError(Application.Match(ComboBox1.Value, _
Worksheets("Sheet1").Columns(1),0) Then
Msgbox "Found"
Else
MsgBox "Not found"
End If
End Sub

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"mcgurkle" wrote in message

s.com...
I have a userform with a number of combo boxes. I would like to be
able to compare all the values, which the user has selected, with a
table on a spreadsheet. The top axis of the table has all the combobox
names on it, and the vertical axis is just numbers from 1 to N. Each
cell in a row of the table may contain a possible value of the combo
box, whose name is at the top of its column. The cell may also be
blank; this represents a don't-care. Each row then represents a
combination of values which are forbidden on the form.

EG. The table might look like this:

¦Combo1Name ¦Combo2Name ¦Combo3Name ¦Combo4Name
---------------------------------------------------------------------------*----------------------------------------------------------
1 ¦Value1 ¦ ¦ValueTuesday ¦ValueMay
2 ¦Value1 ¦Valueb ¦ ¦
3 ¦Value3 ¦ ¦ ¦ValueJuly

Meaning that the values, as represented in rows 1, 2 and 3, cannot be
chosen in combination. If they are, I plan to make a warning appear.
I think I should check this on the change event of the combo boxes,
but I'm just not sure how to go about checking the combinations of
values against the table.
I'd be most grateful for any ideas.

Hi Bob,

Thanks for the quick reply. If I understand correctly, this will check
the value of the combo box individually against the value on the
spreadsheet. If at all possible, I would like to check the value
selected in the combo box - in combination with the values on the
other combo boxes on the userform. That is to say, a warning will only
appear if the values on the same row of the lookup table are selected
at the same time in their respective combo boxes.

So it's ok if I select "Value1" in combo box 1 and "Valuea" in combo
box 2, and it's also ok if I select "Value2" in combo box 1 and
"Valueb" in combo box 2, but if I select "Value1" in combo box 1 and
"Valueb" in combo box 2, then a warning appears.

Is there a way to apply your code to this problem?