Excel macro crashes if an entry in a combo box is not in the list

M

Mike DFR

I am using a combo box on a form to find a part in a list, if the combo is
left empty a message is displayed. the code is as follows
If Trim(Me.cboPart.Value) = "" Then
Me.cboPart.SetFocus
MsgBox "Please enter a part number"
Exit Sub
End If
But if I enter a number not on the list, the debug window pops up.
I can not seem to find the syntax to say 'If the combo value finds no match
then show error message'.
I would think it is just a case of putting the 'no match' in place of the ""
I am greatful for any pointers.
 
B

Barb Reinhardt

Have you looked at

Debug.print "Value: ";Me.cboPart.Value
Debug.print "Length: ",Len(Me.cboPart.text) 'Not sure if this will work
Debug.print "Trim: ", Trim(Me.cboPart.Value)
Debug.print "IsEmpty", ISEMPTY(Me.cboPart) 'Not sure if this will work

This may help you figure out what the problem is.
 
R

Rick Rothstein \(MVP - VB\)

If you change your ComboBox's Style property to 2-fmStyleDropDownList, your
user will not be able to type in anything unless it is in the list.

Rick
 
M

Mike DFR

Rick
Your suggestion stops the crash, but the user is stuck, I need to go from
here to adding a message box an resetting, (reset the combo focus)
Thanks so far.
 
Top