Error when exiting textbox that is blank.

[Darth_Sullivan]

The code below is supposed to look up the value of the number in a textbox named PCNumber and display a returned value in a label named PlayerName. This code works just as it is supposed to, except when the textbox is left blank.

Private Sub PCNumber_Exit(ByVal Cancel As MSForms.ReturnBoolean)

'Active search for the PCNumber and return the PlayerName to the Label
Dim Ans As Variant
Dim myVal As Long
Dim myRng As Range

Set myRng = Worksheets("Sheet1").Range("A1:B65536")

myVal = PCNumber

Ans = Application.VLookup(myVal, myRng, 1, False)

If IsError(Ans) Then
   Ans = "Not found!"
Else
   Ans = Application.VLookup(myVal, myRng, 2, False)
End If

PlayerName = Ans


End Sub

When the textbox is blank and the user clicks out of the textbox, I get:

Run-time error '13':
Type mismatch

When I then hit 'Debug' the piece of code highlighted is:

myVal = PCNumber

I've tried several different ways of trying to avoid this error using an IF block, but without success.

I should also add, at one point, if the textbox never had a entry to begin with, I did not get an error. I would only get the error, during that stage of form completion, when the user would start to type but then backspaced to clear their entry. That problem was solved by placing the code into the 'textbox_exit' routine instead of _change.

How do I keep from getting this error when the textbox is left blank?

Thank you for your time.

 

[Darth_Sullivan]

Solution found.

I decided to attach the code to a button and made the button only visible if the textbox contained anything other than blank. Made the button set to hidden when the form was entered and visible when textbox contained anything other than blank and back to hidden if the textbox became blank again.