+ Reply to Thread
Results 1 to 8 of 8

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

  1. #1
    Registered User
    Join Date
    01-14-2006
    Posts
    12

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

    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

  2. #2
    Toppers
    Guest

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

    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
    >
    >


  3. #3
    Dave Peterson
    Guest

    Re: MatchRequired - how to trap the error when a matching entry is notinput

    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

  4. #4
    Registered User
    Join Date
    01-14-2006
    Posts
    12
    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

  5. #5
    Dave Peterson
    Guest

    Re: MatchRequired - how to trap the error when a matching entry is notinput

    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

  6. #6
    Registered User
    Join Date
    01-14-2006
    Posts
    12
    Quote Originally Posted by Dave Peterson
    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

  7. #7
    Dave Peterson
    Guest

    Re: MatchRequired - how to trap the error when a matching entry is notinput

    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

  8. #8
    Registered User
    Join Date
    05-26-2010
    Location
    Europe
    MS-Off Ver
    Excel 2003
    Posts
    1

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

    The listboxstyle is exactly what I needed to solve the problem of the error message after a user has deleted the entry in the combobox.

    it is strange though that the error message fires before any of the _change, _beforeUpdate events fire.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1