Excel VBA Textbox_Exit Event Handler

Question

At a road block here. Simple user form using three text boxes, one for user id, two to enter serial number using hand scanner. User loads excel file, userform.show loads, user enters id then simple validation to verify numberic number, then focus set on first textbox, user scans barcode to input serial number, again simple validation to ensure numeric and length, same with last textbox, scan serial number, validate first textbox entry matches second textbox entry.

Hand scanner is used to input serial number and also returns a "return carriage" character; e.g. enter button press after serial number scan.

Using "return carriage" to fire textbox_exit event handler. Issue is very intermittent but consistent. I load userform, input data, when record is complete, data is transferred to object worksheet. However, when troubleshooting, I initially open workbook and userform, create a few records, save, and close. Everything works well and data is recorded and archived. Issue generally arises when i load workbook a second time, enter data for one record, save, and begin second record. Once serial number is entered into first textbox, exit event never fires using "return carriage". I can manually transfer focus to other objects; e.g. diff textbox, but the overall operation is not as expected.

I have tried inserting application.eventhandler=true commands, different event handlers, as well as numerous code changes; e.g. exit sub at end of IF statements, to make this work.

Thought I would reach out to the community for some feedback. FYI, issues still arises if I simulate hand scanner using copy/paste and enter key.

Example of exit event handler for first serial textbox below.

Private Sub SerialIn_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Cancel = False

If Not IsNumeric(SerialIn.Value) Then        'validate serial number is numeric

    'error msg serial number is not numeric
    Msg = "Opps, something went wrong!  Serial number was incorrect." _
        & vbNewLine & vbNewLine & "Rescan module serial number."
        MsgBox Msg, vbCritical, "Warning"           'display msg

    Cancel = True                               'stop user from changing focus

    SerialIn.SelStart = 0                       'highlight user text
    SerialIn.SelLength = Len(SerialIn.Value)    'select user text
    'Image1.Picture = LoadPicture(StopLightRed)  'display red stop light
    Exit Sub
Else
    If Not Len(SerialIn.Value) = 19 Then           'validate serial number length
        'error msg incorrect length
        Msg = "Opps, something went wrong!  Serial number was incorrect." _
            & vbNewLine & vbNewLine & "Rescan module serial number."
            MsgBox Msg, vbCritical, "Warning"

        Cancel = True                               'stop user from changing focus

        SerialIn.SelStart = 0                       'highlight user text
        SerialIn.SelLength = Len(SerialIn.Value)    'select user text
        'Image1.Picture = LoadPicture(StopLightRed)  'display red stop light
        Exit Sub
    Else
        SerialInTime.Caption = Now                      'record date and time
        'Image1.Picture = LoadPicture(StopLightYellow)   'display yellow WIP stop light
        Me.SerialOut.SetFocus
        Exit Sub
    End If

End If End Sub

New code:

Private Sub SerialIn_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Cancel = ValidateSerialIn(SerialIn)
End Sub

Function ValidateSerialIn(ByVal TextBox As Object) As Boolean

If Not IsNumeric(SerialIn.Value) Then        'validate serial number is numeric
'error msg serial number is not numeric
Msg = "Opps, something went wrong!  Serial number was incorrect." _
    & vbNewLine & vbNewLine & "Rescan module serial number."
    msgbox Msg, vbCritical, "Warning"           'display msg

SerialIn.SetFocus
SerialIn.SelStart = 0                       'highlight user text
SerialIn.SelLength = Len(SerialIn.Value)    'select user text
'Image1.Picture = LoadPicture(StopLightRed)  'display red stop light

ValidateSerialIn = True

Else
    If Not Len(SerialIn.Value) = 19 Then           'validate serial number length
'error msg incorrect length
Msg = "Opps, something went wrong!  Serial number was incorrect." _
    & vbNewLine & vbNewLine & "Rescan module serial number."
msgbox Msg, vbCritical, "Warning"

'Cancel = True                               'stop user from changing focus

SerialIn.SelStart = 0                       'highlight user text
SerialIn.SelLength = Len(SerialIn.Value)    'select user text
'Image1.Picture = LoadPicture(StopLightRed)  'display red stop light

ValidateSerialIn = True
Else
    SerialInTime.Caption = Now                      'record date and time
    'Image1.Picture = LoadPicture(StopLightYellow)   'display yellow WIP stop light

    ValidateSerialIn = False
    End If
End If
End Function

Third go using Tim's TextBox_Change solution:

Private Sub SerialIn_Change()
Dim v
v = ScannedValue1(SerialIn.Text)

If Len(v) > 0 Then
    If Not IsNumeric(v) Then        'validate serial number is numeric
        'error msg serial number is not numeric
        Msg = "Opps, something went wrong!  Serial number was incorrect." _
            & vbNewLine & vbNewLine & "Rescan module serial number."
        msgbox Msg, vbCritical, "Warning"           'display msg

        SerialIn.Text = vbNullString
    Else
        If Not Len(v) = 19 Then           'validate serial number length
            'error msg incorrect length
            Msg = "Opps, something went wrong!  Serial number was incorrect." _
                & vbNewLine & vbNewLine & "Rescan module serial number."
            msgbox Msg, vbCritical, "Warning"

            SerialIn.Text = vbNullString
            'Image1.Picture = LoadPicture(StopLightRed)  'display red stop light
        Else
            SerialInTime.Caption = Now                      'record date and time
            'Image1.Picture = LoadPicture(StopLightYellow)   'display yellow WIP stop light
            SerialOut.SetFocus
        End If
    End If
End If
End Sub

'check if a value ends with vbcrlf or vblf
' - if yes strip that off and return the rest
' - otherwise returns empty string
Function ScannedValue1(vIn) As String
Dim rv As String
If Right(vIn, 2) = vbCrLf Then
    ScannedValue1 = Replace(vIn, vbCrLf, "")
ElseIf Right(vIn, 1) = vbLf Then
    ScannedValue1 = Replace(vIn, vbLf, "")
End If
End Function

Answer 1

If you want to detect the "Enter" from the scanner then use the Change event to check if the textbox value ends with vbCrLf or vbLf (in that order): if it does, then trigger the "scan" action.

Note you need to set your textbox to "multiline=true" and "EnterKeyBehaviour = true" in order for the Change event to capture the enter key.

Private Sub TextBox1_Change()

    Dim v

    v = ScannedValue(TextBox1.Text)

    If Len(v) > 0 Then
        TriggerScanAction v
        TextBox1.Value = ""
    End If

End Sub

'check if a value ends with vbcrlf or vblf
' - if yes strip that off and return the rest
' - otherwise returns empty string
Function ScannedValue(vIn) As String
    Dim rv As String
    If Right(vIn, 2) = vbCrLf Then
        ScannedValue = Replace(vIn, vbCrLf, "")
    ElseIf Right(vIn, 1) = vbLf Then
        ScannedValue = Replace(vIn, vbLf, "")
    End If
End Function

'execute some action triggered by a scanned value
Sub TriggerScanAction(v)
    MsgBox "You scanned " & v
End Sub