How do I display a message box if an error occurs and stop the error too? I
want to skip/stop an error if a user enters in a wrong 'ssn' from a 'ssn
combobox', and have a message pop up "ssn is not listed" in lieu of
'end..debug'. I am sure this is possible, but I am at best an experienced
'newbie'.

Example: I have a user form in which a user can select from a 'ssn combobox'
an ssn. Also the user can tab into the 'ssn combobox', and type the
appropriate numbers. The problem is if the user types in a 'ssn' that is not
in a database (hidden worksheet) which a macro is indexing-matching, then I
get an error message 'end..debug'.

What I've done thus far: I have used 'on error resume...', which works fine.
But I can't figure out how to get a message box to pop up and tell the user
"ssn isn't listed". And I played with the combobox properties and changed the
style to 'dropdownlist', but I found through testing that if I type '123'
wait a couple seconds and then type some more numbers '456', the selection
starts with the next number I typed (all ssns that start with 456, instead of
123456). So I changed the style back to 'dropdowncombo'.

End state: What I would like is that when the user enters a ssn that isn't
listed, a message box is displayed "ssn isn't listed", and for the error to
be skipped/stoppeed, as I don't want the user to get into the vba.

Please help.

Hi,
Using the built-in features of the combobox, you can link it the specific
range on the hidden sheet, then either use the MatchRequired property or
check the ListIndex property:

Say data is in Sheet1!a1:A10; you have a COmbobox1 combo and a Go button
that the user hits once he has chosen a ssn.

- Link to Sheet1!A1:A10
- select the combobox on the form and set its RowSource property to:
Sheet1!A1:A10

- Using MatchRewuired
- Select the combo on the form and set its Match reuired property to True
- Try the form. A message pops-up evrytime a wrong entry is entered.
However the fact that it porevents the user to leave the combo unless a valid
entry is made can be a problem.

You can therefore try the folowing alternative:
- Using the Go button described above
- Reset the MatchEntry of the combo to False
- Manage the entry validation in the Go_Click event sub
Sub Go_Click( )
'check the LIstIndex prop of the Combo. If -1 then no matching entry
If Combobox1.listindex = -1 then
Msgbox "'" & Combobox1.Value & "' is not a valid ssn. Please
try again."
Exit Sub
End If

' Here, bellow, treat the valid case
' ....
End sub
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"BigPig" wrote:

How do I display a message box if an error occurs and stop the error too? I
want to skip/stop an error if a user enters in a wrong 'ssn' from a 'ssn
combobox', and have a message pop up "ssn is not listed" in lieu of
'end..debug'. I am sure this is possible, but I am at best an experienced
'newbie'.

Example: I have a user form in which a user can select from a 'ssn combobox'
an ssn. Also the user can tab into the 'ssn combobox', and type the
appropriate numbers. The problem is if the user types in a 'ssn' that is not
in a database (hidden worksheet) which a macro is indexing-matching, then I
get an error message 'end..debug'.

What I've done thus far: I have used 'on error resume...', which works fine.
But I can't figure out how to get a message box to pop up and tell the user
"ssn isn't listed". And I played with the combobox properties and changed the
style to 'dropdownlist', but I found through testing that if I type '123'
wait a couple seconds and then type some more numbers '456', the selection
starts with the next number I typed (all ssns that start with 456, instead of
123456). So I changed the style back to 'dropdowncombo'.

End state: What I would like is that when the user enters a ssn that isn't
listed, a message box is displayed "ssn isn't listed", and for the error to
be skipped/stoppeed, as I don't want the user to get into the vba.

Please help.

set the MatchRequired property to True

or change the Style property to

fmStyleDropDownList
as you did, but also change

matchentry to FmMatchEntryNone



Combobox1.Style= fmStyleDropDownList
Combobox1.MatchEntry = FmMatchEntryNone

Then the combobox doesn't show anything until the dropdown is actually used.

--
Regards,
Tom Ogilvy


"BigPig" wrote in message
...
How do I display a message box if an error occurs and stop the error too?
I
want to skip/stop an error if a user enters in a wrong 'ssn' from a 'ssn
combobox', and have a message pop up "ssn is not listed" in lieu of
'end..debug'. I am sure this is possible, but I am at best an experienced
'newbie'.

Example: I have a user form in which a user can select from a 'ssn
combobox'
an ssn. Also the user can tab into the 'ssn combobox', and type the
appropriate numbers. The problem is if the user types in a 'ssn' that is
not
in a database (hidden worksheet) which a macro is indexing-matching, then
I
get an error message 'end..debug'.

What I've done thus far: I have used 'on error resume...', which works
fine.
But I can't figure out how to get a message box to pop up and tell the
user
"ssn isn't listed". And I played with the combobox properties and changed
the
style to 'dropdownlist', but I found through testing that if I type '123'
wait a couple seconds and then type some more numbers '456', the selection
starts with the next number I typed (all ssns that start with 456, instead
of
123456). So I changed the style back to 'dropdowncombo'.

End state: What I would like is that when the user enters a ssn that isn't
listed, a message box is displayed "ssn isn't listed", and for the error
to
be skipped/stoppeed, as I don't want the user to get into the vba.

Please help.

Hi Sebastien,

Thankyou for your suggestion!

It worked great! What I ended up doing was using your last suggestion:

If ComboBox5.ListIndex = -1 Then
MsgBox "The SSN isn't listed in the current DB, you will have to manually
type in the data in the follow on fields."
ComboBox5.Value = "SSN_SM" 'brings up the default value

Exit Sub
End If

'my macro

End Sub

Thanks again!

Tom,

Thank you also for your suggestion!

I tried it and it works like a charm too.

As always, your solutions are right on target.