Change of TextBox Value

[swisha]

Hello Everyone,

I'm working on a simple GUI to input serial numbers. I have created a textbox in a userform to allow a input of a serial number using a hand scanner. The scanner will read serial number and submit an Enter command upon completion of input. Using the Enter command allows the user to progress within the userform into the next textbox.

I have setup some simple conditions to validate the entry within the TextBox_AfterUpdate sub. In the event the input does not meet these conditions, a msgbox informs the user to reenter the serial number. After acknowledging of the msgbox prompt, the sub returns focus to the textbox and highlights the incorrect serial number. Routine should then exit the AfterUpdate sub. When executing the userform all appears to function as intended and the incorrect entry is indeed highlighted if validation conditions are not met.

My thought process was to allow the user to correct their entry with the hand scanner which upon completion of the new entry, would trigger the TextBox_AfterUpdate sub again and begin validation of the new entry. This was the intent, however, the userform just hangs after the new entry and it doesn't appear the AfterUpdate sub is being called even tho the input has changed and Enter command issued from scanner. When using debug mode the routine does indeed hang after exiting the AfterUpdate sub, yet never highlights the incorrect input.

Any thoughts on the way im approaching this problem? Am I using the AfterUpdate sub incorrectly or simple not executing the logic correctly after the textbox reentry event? Any advice would be appreciated.

Private Sub SerialIn_AfterUpdate()
'Begin processing user input.

If Len(SerialIn.Value) = 19 Then    'validate serial number length
    SerialInTime.Caption = Now      'record date and time
    
    SerialOut.SetFocus              'set focus on next textbox
Else
    Dim Msg As String               'set error message
    Msg = "Opps, something went wrong!  Serial number was incorrect." _
        & vbNewLine & vbNewLine & "Rescan module serial number."
    msgbox Msg, vbCritical, "Warning"
    
    SerialIn.SetFocus               'return focus to textbox
    SerialIn.SelStart = 0           'highlight user text
    SerialIn.SelLength = 99
End If

End Sub

 

[swisha]

After some additional review, it appears my next textbox object was disabled and therefore setfocus could not be transferred and trigger AfterUpdate sub. The textbox enable/disable approach was intended to control the order in which textboxes are filled out. If I enable the textbox, the AfterUpdate sub fires correctly, however, there are now some additional controls needed to ensure user inputs data in chronological order. Will experiment with locked feature and possibly background color changes to guide the user.

 

[swisha]

Bah. Lock control seems to be causing other issues; more setfocus problems. Back to the drawing board…

FYI summary of expected outcome:

TextBox1 'user enters a serial number
-IF Len 'if serial number length OK, record date and time and proceed to TextBox2
-Else 'user to re enter serial number using scanner
-Recheck serial length by triggering AfterUpdate sub again

**User to perform external activity**

TextBox2 'enters same serial number as above using scanner
-IF to check TextBox1.Value=TextBox2.Value 'if OK setfocus on Submit button
-Else 'user to re enter serial number using scanner
-Recheck serial match by triggering AfterUpdate sub again

Conditions:
User must input serial number in TextBox1 first
If serial number correct, proceed to TextBox2
User now cannot edit TextBox1
Serial number in TextBox2 must match TextBox1
If serial numbers match, user is allowed to submit data using button, else re enter serial number
User not allowed to use button unless all data has been entered and matches.

Normally I would do all of this with the Submit button sub, however, due to the application, I’m needing to process and evaluate the data at each step. Given the hand scanner returns serial number with an Enter command, I thought AfterUpdate would be the appropriate sub for this.

 

[swisha]

After some research, im am utilizing the exit sub procedure to begin data validation and using cancel to keep user in textbox control.

Now just need to prevent msgbox prompt if user abruptly exits userform. Getting there.

Private Sub SerialIn_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Len(SerialIn.Value) <> 19 Then           'validate serial number length

Dim Msg As String                           'set error message
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)
Else
SerialInTime.Caption = Now                  'record date and time
End If
End Sub

 

[swisha]

It appears the above code for SerialIn_Exit works well. However when applied to the other textbox, nothing. It does not appear the sub is called upon exit of textbox2 only when manually exiting the userform. Strange.

 

[swisha]

Seems to be an ongoing issue with frames and controls. Neither Exit nor AfterUpdate subs fire correctly. Linked discussion thread below.

Solution, drop frames around my textbox and sub work beautifully. If anyone has a workaround they would like to share, please feel free to advise.

https://www.mrexcel.com/forum/excel..._exit-event-2.html?highlight=exit+event+frame