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

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.

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

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.
--
HTH,
Barb Reinhardt



"Mike DFR" wrote:

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.

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

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


"Mike DFR" wrote in message
...
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.

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

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.

"Rick Rothstein (MVP - VB)" wrote:

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


"Mike DFR" wrote in message
...
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.