MatchRequired - how to trap the error when a matching entry is not input

  • Thread starter Thread starter exceltim
  • Start date Start date
E

exceltim

Hello guys,

This is my first post here, and I'm wondering if anyone can help me.

I have a simple excel form with a combo box whose MatchRequire
property is set to True.

I would like the user to receive a more user-friendly message than th
"invalid property value" that comes as standard, but I don't know ho
to trap the error because I don't know which event is firing when th
error occurs - I've tried my error handler in the "Before Update"
"Exit" and a few other events associated with the combo box, but to n
avail.

Can anyone suggest a solution?

Thanks,

Ti
 
Hi,
Set MatchRequired to False and try this:


Private Sub ComboBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

If ComboBox1.MatchFound = True Then
MsgBox "Match Found" <===== Your code as required
Else
MsgBox ComboBox1.Value & " is an invalid entry"
End If

End Sub

HTH
 
Maybe changing the .style property from fmStyleDropDownCombo to
fmStyleDropDownList would help.
 
Thanks very much for your suggestions guys....

I ended up doing something similar to Toppers' suggestion (I didn't
want to change it to a listbox because of the nature & purpose of the
form).... here's my code:

Private Sub ComboBox1_BeforeUpdate(ByVal Cancel As
MSForms.ReturnBoolean)
If ComboBox1.MatchFound = False And ComboBox1.Text <> "" Then
MsgBox "Invalid entry. Please select from the available list"
Cancel = True
ComboBox1.Text = ""
End If
End Sub

Cheers,

Tim
 
I didn't suggest using a listbox. I suggested changing the style from a
combobox--where you can choose from the list or type something new to a combobox
where you can only type what's in the list (or choose from that list).

You may want to look at that one more time.
 
Dave said:
I didn't suggest using a listbox. I suggested changing the style from a
combobox--where you can choose from the list or type something new to
combobox
where you can only type what's in the list (or choose from that list).
Click to expand...

I'm very sorry Dave.... I was not long out of bed when I read your pos
(was early morning here in Australia) and I must have still been hal
asleep! I can see what you're saying now.

I had a little play with it, and it looks like a great idea.... I'll d
some more testing with the spreadsheet before deciding on which optio
to use.

Sorry again for the misunderstanding, and thanks very much for you
help!

Ti
 
Back
Top