Excel VBA Textbox_Exit事件处理程序 [英] Excel VBA Textbox_Exit Event Handler

问题描述

在这里的路障.使用三个文本框的简单用户表单，一个用于用户ID，两个用于使用手动扫描仪输入序列号.用户加载excel文件，userform.show加载，用户输入id然后简单验证以验证数字，然后将焦点放在第一个文本框上，用户扫描条形码以输入序列号，再次简单验证以确保数字和长度与最后一个文本框相同，扫描序列号，确认第一个文本框条目与第二个文本框条目匹配.

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.

使用回车"来触发textbox_exit事件处理程序.问题是断断续续的，但始终如一.我加载用户窗体，输入数据，记录完成后，数据将传输到对象工作表.但是，在进行故障排除时，我最初会打开工作簿和用户窗体，创建一些记录，然后保存并关闭.一切正常，数据被记录和存档.当我第二次加载工作簿，输入一条记录的数据，保存并开始第二条记录时，通常会出现问题.在第一个文本框中输入序列号后，就不会使用回车"触发退出事件.我可以手动将焦点转移到其他对象；例如diff文本框，但总体操作与预期不符.

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.

我尝试插入application.eventhandler = true命令，不同的事件处理程序以及许多代码更改；例如在IF语句的末尾退出sub，以使此工作有效.

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

如果结束 结束

新代码:

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

第三次使用Tim的TextBox_Change解决方案:

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

推荐答案

如果要从扫描仪检测到"Enter"，请使用Change事件检查文本框值是否以vbCrLf或(按此顺序):如果是，则触发扫描"操作.

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.

请注意，您需要将文本框设置为"multiline = true"和"EnterKeyBehaviour = true"，以便Change事件捕获Enter键.

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

这篇关于Excel VBA Textbox_Exit事件处理程序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！