Error Handling question

[Guest]

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.

 

[Guest]

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

 

[Tom Ogilvy]

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.

 

[Guest]

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!

 

[Guest]

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.