ComboBox match issue

[IanC]

I have several ComboBoxes on a form.
These ComboBoxes are populated from ranges specified in RowSource.
Initially the ComboBoxes display Select as set by the Value property.
If the user selects from the available options there is no problem as the
subsequent code recognises the input.

When all the relevant data is selected, an OK button is enabled. If not,
then there is only a Cancel button.

If the MatchRequired property is set to True, clicking the Cancel button on
the form displays "Invalid property value" and the code associated with the
Cancel button fails to execute.

I suspect it is connected with each of the ComboBoxes initially containing
"Select" even though this is not in the RowSource range.

I think I have 2 options:
1. Set MatchRequired to False. Unfortunately this allows the user to type
into the ComboBox, potentially causing the subsequent code to crash.
2. Add "Select" to the range specified in RowSource. This means that
"Select" will appear in the and is a relatively minor issue.

Is there a 3rd way? Ideally I'd like to disable the option to type into the
ComboBox. I know I could use a ListBox, but I can't enter "Select" initially
unless it's in the specified range, in which case I'm better going with
option 2 as the ComboBoxes are already in place.

 

[Ryan H]

You should post your code that you have in your Cancel Button _Click Event
and the code that is ran to enable the Cancel Button. Since you didn't post
any code I will have to guess what you need. Try this. Hope this helps! If
so, let me know, click "YES" below.

Put this in your Cancel Button Click Event

Unload Me

 

[IanC]

Hi Ryan

I didn't post the code as it's largely irrelevant. My question is whether
there is some way to stop users from typing into a ComboBox but still allow
the "Select" to be displayed even though it isn't in the RowSource range.

For what it's worth, the Cancel button code is posted below.

Private Sub CommandButton2_Click()
UserForm1.Hide
Call Worksheets("Rooms").UserForm_Reaction
With ActiveWindow
.WindowState = xlMaximized
End With
Worksheets("Rooms").Range("B1").Select
Worksheets("Rooms").Range("B2").Select
End Sub

 

[Ryan H]

Ok, you have a few options.

1.) You could add the word "Select" to the range you reference for you
ComboBox. Change your RowSource range. Then put this line of code in the
Userform_Intialize Event

ComboBox1.Value = "Select"

2.) At design time you code just type in the word "Select" into the
ComboBox. When the userform is displayed the word "Select" will be in the
combobox, but when the user clicks the combobox to select something the word
"Select" will not be there.

3.) You could use this code in the Userform_Intialize Event.

With ComboBox1
.AddItem "Select"
.Value = "Select"
End With

Hope this helps! If so, let me know, click "YES" below.

 

[IanC]

Thanks Ryan. I'm resigned to having "Select" in the RowSource range so that
I can enable MatchRequired. It seems this is the only way to allow void the
possibility of having a user type into the ComboBox.

Unfortunately, there may be instances where a user needs to hit Cancel while
Select still appears in one or more ComboBoxes. The only other alternative
would be to leave the ComboBoxes blank.