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

--
excelti
-----------------------------------------------------------------------
exceltim's Profile: http://www.excelforum.com/member.php...fo&userid=3046
View this thread: http://www.excelforum.com/showthread.php?threadid=50131

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

"exceltim" wrote:


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 MatchRequired
property is set to True.

I would like the user to receive a more user-friendly message than the
"invalid property value" that comes as standard, but I don't know how
to trap the error because I don't know which event is firing when the
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 no
avail.

Can anyone suggest a solution?

Thanks,

Tim


--
exceltim
------------------------------------------------------------------------
exceltim's Profile: http://www.excelforum.com/member.php...o&userid=30464
View this thread: http://www.excelforum.com/showthread...hreadid=501310

Maybe changing the .style property from fmStyleDropDownCombo to
fmStyleDropDownList would help.



exceltim wrote:

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 MatchRequired
property is set to True.

I would like the user to receive a more user-friendly message than the
"invalid property value" that comes as standard, but I don't know how
to trap the error because I don't know which event is firing when the
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 no
avail.

Can anyone suggest a solution?

Thanks,

Tim

--
exceltim
------------------------------------------------------------------------
exceltim's Profile: http://www.excelforum.com/member.php...o&userid=30464
View this thread: http://www.excelforum.com/showthread...hreadid=501310

--

Dave Peterson

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


--
exceltim
------------------------------------------------------------------------
exceltim's Profile: http://www.excelforum.com/member.php...o&userid=30464
View this thread: http://www.excelforum.com/showthread...hreadid=501310

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.

exceltim wrote:

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

--
exceltim
------------------------------------------------------------------------
exceltim's Profile: http://www.excelforum.com/member.php...o&userid=30464
View this thread: http://www.excelforum.com/showthread...hreadid=501310

--

Dave Peterson

Dave Peterson Wrote:
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).

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

--
excelti
-----------------------------------------------------------------------
exceltim's Profile: http://www.excelforum.com/member.php...fo&userid=3046
View this thread: http://www.excelforum.com/showthread.php?threadid=50131

Not a problem. But it is a pretty easy thing to try--even if it fails.

exceltim wrote:

Dave Peterson Wrote:
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).

I'm very sorry Dave.... I was not long out of bed when I read your post
(was early morning here in Australia) and I must have still been half
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 do
some more testing with the spreadsheet before deciding on which option
to use.

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

Tim

--
exceltim
------------------------------------------------------------------------
exceltim's Profile: http://www.excelforum.com/member.php...o&userid=30464
View this thread: http://www.excelforum.com/showthread...hreadid=501310

--

Dave Peterson