TextBox Exit Event

swisha

New Member
Joined
Aug 31, 2019
Messages
9
Hello Everyone,

Have used this site many times as resource for my excel vba needs. Was hoping an individual could with a specific problem involving Win10.

I have developed a userform to assist in a rework process. Intended result is user to enter a serial number using a textbox, if conditions are met, date and time are recorded and next textbox is given focus. The user again enters the same serial number, this time in the second textbox and again, if conditions are met, date and time are recorded. This activity is meant to establish date and time bookend for additional data analysis and works as expected. Following completion of entering data into the userform, and when conditions are met, the userform enables FinishBtn button to archive collected data and begin processing a new record.

Issue arises when using the FinishBtn to archive data and return focus to the first textbox. When using Win7 and Excel2016, the button code functions as expect, archives the data, returns focus to textbox, user can enter serial number and exit handler fires as expected. Issue is Win10 and I believe Excel2016. When executing button code, data archives, and focus is returned to first textbox, however the exit handler does not fire. In order to resolve this, I manually remove focus from the userform (click another open application), then reactivate the userform using a mouse click anywhere on the form. The textbox exit handler will then fire and all is well.

I must note I am using a hand scanner that returns Enter upon completion of scan. Also worth noting, the exit handler functions fine if using the enter button. I assume there are issues with the focus of the userform/textbox when using handscanner.

Another point to note, this only happens once, after the first execution of the FinishBtn code. After completion of the second entry, all is well.


Summary of logic:
TextBox1; user input, exit handler fires
TextBox2; user input, exit handler fires
FinishBtn; enabled and archives data and return focus to TextBox1
TextBox1; user input, exit handler does NOT fire
Solution, remove focus from user form, return focus to user form, select TextBox with user entry, press Enter, exit handler fires.
Wash rise repeat, without issue.


Just not understanding why this occurs only when using scanner, only after first execution of FinishBtn. Any thoughts or help would be greatly appreciated. Example code below. Thanks in advance!

Code:
Private Sub SerialIn_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Not IsNumeric(SerialIn.Text) Then        'validate serial number is numeric
    'error msg
    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
Else
    If Not Len(SerialIn.Value) = 19 Then           'validate serial number length
        'error msg incorrect length
        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
    Else
        'write some information
    End If
End If
End Sub

Private Sub SerialOut_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Not SerialIn.Value = SerialOut.Value Then    'validate serial scanned in equals serial scanned out
    'error msg
    msgbox Msg, vbCritical, "Warning"

    Cancel = True                               'stop user from changing focus
    SerialOut.SelStart = 0                      'highlight user text
    SerialOut.SelLength = Len(SerialOut.Value)  'select user text
Else
    'write some information
End If
End Sub

Private Sub NextModuleBtn_Click()
    'archive data to worksheet
    'save workbook
    SerialIn.SetFocus           'return focus to first textbox for next entry
End Sub
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

swisha

New Member
Joined
Aug 31, 2019
Messages
9
Changing focus statement in last line of button function to Me.SerialIn.SetFocus (Me.TextBox.SetFocus) seemed to have fixed the glitch. Anyone noticed any other nuances with respects code execution in WIN10?
 

swisha

New Member
Joined
Aug 31, 2019
Messages
9
Ugh, still having issues with this. User enters data into text box using hand scanner which scans serial number then returns "Enter" character (would normally progress cursor to next line if using notepad). Intent is "Enter" character is to fire exit event. Seems to work sporadically at best like the code is hanging. For example, I can populate first record, consisting of two text boxes and serial number, then archive record and clear form. Upon second record, a new serial number is scanned, however, exit event does not fire. If I manually click the next textbox the exit event will then fire.

My question, is there different event I should call, e.g. afterupdate, change, etc., to receive the desired effect? Again, want to use the scanners "Enter" character to exit textbox and move focus to next text box. Calling the next object (textbox) manually with code does not help due to event not firing.

Any direction would be appreciated.
 

Watch MrExcel Video

Forum statistics

Threads
1,090,551
Messages
5,415,227
Members
403,573
Latest member
ballardgirl

This Week's Hot Topics

Top