Two Problems With My Userform Textbox Control

[Ark68]

Please consider this code:

Public emplnum As Variant
Public pw As String
Public eqtnum As Double
Private Sub tb_emplnum_exit(ByVal Cancel As MSForms.ReturnBoolean)
    Stop
    emplnum = Me.tb_emplnum.Value
    If IsNumeric(emplnum) = False Then
        MsgBox "Please enter a valid employee number" & Chr(13) & "{#####}", vbInformation, "ERROR"
        Me.tb_emplnum.Value = ""
        Me.tb_emplnum.SetFocus
        Exit Sub
    End If
    If Len(emplnum) < 5 Or Len(emplnum) > 5 Then
        MsgBox "Please enter a valid employee number" & Chr(13) & "{#####}", vbInformation, "ERROR"
        Me.tb_emplnum.Value = ""
        Me.tb_emplnum.SetFocus
        Exit Sub
    End If
    emplnum = CDbl(emplnum)
    If Application.WorksheetFunction.CountIf(Worksheets("pw").Columns(1), emplnum) < 1 Then
        MsgBox "User not permitted.", vbExclamation, "UNAUTHORIZED"
        Me.tb_emplnum.Value = ""
        Me.tb_emplnum.SetFocus
        Exit Sub
    End If
    Me.tb_password.Enabled = True
End Sub

Private Sub tb_password_AfterUpdate()
    pw = Me.tb_password.Value
    If Len(pw) < 1 Or Len(emplnum) > 16 Then
        MsgBox "Invalid password.", vbInformation, "ERROR"
        Me.tb_password.Value = ""
        Me.tb_password.SetFocus
        Exit Sub
    End If
    If pw <> Application.WorksheetFunction.VLookup(emplnum, Worksheets("pw").Range("A1:B500"), 2, False) Then
        MsgBox "Incorrect password.", vbExclamation, "ERROR"
        Me.tb_password.Value = ""
        Me.tb_password.SetFocus
        Exit Sub
    End If
    Me.check1.Visible = True
    Me.Label3.Visible = True
    Me.tb_eqtnum.Visible = True
    Me.tb_eqtnum.Enabled = True
    Me.tb_eqtnum.SetFocus
    Me.btn_proceed.Enabled = True
   
End Sub

Private Sub UserForm_Initialize()
    mbevents = False
    With Me
        .tb_emplnum = ""
        .tb_password = ""
        .tb_password.Enabled = False
        .tb_eqtnum = ""
        .tb_eqtnum.Enabled = False
        .tb_eqtnum.Visible = False
        .Label3.Visible = False
        .check1.Visible = False
       ' .btn_proceed.Enabled = False
    End With
    mbevents = True
End Sub

Problem #1
The textbox control "tb_emplnum" is empty on userform initialization. After a user enters a value, the value for emplnum = "", which triggers a condition to flag an error. The user then enters the same value, and this time it works. AFter the second entry it works. Why won't it recognize the value on the first entry?

Problem #2
I have the tabs all in order for 0 to 6 (labels and textboxs). The labels have TabStop set to false. When a user tabs out of a control without error, I am expecting it to go to the next control. Similarly, if the user errors in an entry, the user is sent back to that control via the Setfocus command. But what I am finding, is that regardless of how the user gets into a control, unless he clicks that control, there is no cursor. Is there something I'm missing? If user moves to a new control, or sent back to retry, how can I eliminate the need for the user to click the control, and just allow the user to simply start typing?

I've had to comment out the line in the initialization procedure that disables the submit button (Tab 6). With it disabled, I was unable to tab out of "tb_emplnum" (Tab 2). Only with it enabled could I resume moving between controls.

I am finding that after the user successfully enters a password in "tb_password" (Tab 4), that tabbing out sends focus to the "tb_empl_num" control (Tab 1) when it should be going to "tb_eqtnum". (Tab 5)

Any help with these questions is greatly appreciated.

 

[Micron]

I mentioned using BeforeUpdate in post 2. Haven't seen file yet thus didn't know controls where being disabled. Pretty sure that takes them out of the tab order so that doesn't seem like a good idea to me either. As for redacting, if you're good at adapting code there is a function here that randomizes data in Access tables. If you can copy/paste the relevant parts into Excel procedure, that might help with your sheets should you decide to post the primary file.

 

[Ark68]

I am providing my working file, for which I made edits to my form code to eliminate any instances of enable and disable. I have changed my events to BeforeUpdate and altered my code to include (based on my understanding of Cancel) Cancel = True after each test.

I continue to have these problems:

1) Enter any 5 digit number (required) in the employee number box. I get an error "(01) Please enter a valid employee number" meaning it didn't recognize the value entered as being a number. Doesn't matter which 5 digit number you enter, the first time around it errs. Cancel kicks in and the user is positioned to retry. Entering the same number this time is recognized and the code continues. This for some reason wasn't happening with the previous file I shared - not sure how it differs to create that behaviour.

2) Tabs are still out of order. After the employee number is accepted, tabbing takes you to the submit button. Another Tab takes you back to, and selects, the employee number entry. A further tab takes you to the password field which is where the first tab is supposed to send the user. My tab sequence is set up in my form as:

Employee number label	0	Tabstop = False
Employee number textbox	1	Tabstop = True
Password label	2	Tabstop = False
Password textbox	3	Tabstop = True
Eqt number label	4	Tabstop = False
Eqt number textbox	5	Tabstop = True
Submit button	7	Tabstop = true

3) New problem - if I enter a value, whether complete or incomplete into the employee number textbox, and press the 'X" to close the form window, two custom error messages relating to the value entered into the employee number textbox popup before the form closes. If you don't enter a value, and close, there is no issue.

 

[Micron]

You were using .Value which would not exist because that property would not be set the first time. Turns out that the first time through in beforeupdate the Text property isn't set yet either, so the control contains "". I would expect that for Value property but not Text. Pretty sure it would not be the case in Access but would have to test. That explains why the first time it doesn't work. Even when the event is canceled, the value and text properties have become set so the second time it is not "".

Suggest you save validation for when user clicks the button then check both. If not, possibly going back to the exit event and see if the focus issue can be fixed. In Access, Change event fires on each keypress so I don't know if that's a good choice for your situation. This one might take some time to solve (couple of days) either way given that I've got chores calling me. I'm trying not to listen, but the noise in my head is getting too loud.

 

[dmt32]

Hi,
Done very quickly & may need some adjustment but see if this slightly different approach would work for you

Make a backup of your workbook & then delete all existing codes

Place all codes in your userform code page

Dim wsPassword      As Worksheet
Public emplnum      As Variant
Public pw           As String
Public eqtnum       As Double
Private Sub btn_proceed_Click()
    
    With ws_form
        .Range("O3").Value = emplnum
        .Range("O4").avalue = eqtnum
    End With
    Unload Me
    
End Sub

Private Sub tb_emplnum_Change()
    Dim IsValid     As Boolean
    With Me.tb_emplnum
        emplnum = .Value
        m = Application.Match(Val(.Value), wsPassword.Columns(1), 0)
        IsValid = Len(.Value) = .MaxLength And Not IsError(m)
        .BackColor = IIf(IsValid, vbGreen, IIf(Len(.Value) = 0, vbWhite, vbRed))
    End With
    
    With Me.tb_password
        .Enabled = IsValid
        If IsValid Then .SetFocus: .Tag = m
    End With
    
End Sub

Private Sub tb_password_Change()
    Dim IsValid     As Boolean
    
    With Me.tb_password
        pw = .Value
        IsValid = wsPassword.Cells(Val(.Tag), 2).Value = Me.tb_password
        .BackColor = IIf(IsValid, vbGreen, IIf(Len(.Value) = 0, vbWhite, vbRed))
    End With
    
    Me.check1.Visible = IsValid
    Me.Label3.Visible = IsValid
    
    With Me.tb_eqtnum
        .Visible = IsValid
        .Enabled = IsValid
        If .Enabled Then .SetFocus
    End With
End Sub

Private Sub tb_eqtnum_Change()
    Dim IsValid     As Boolean
    
    With Me.tb_eqtnum
        eqtnum = Val(.Value)
        IsValid = Len(.Value) = .MaxLength
        .BackColor = IIf(IsValid, vbGreen, IIf(Len(.Value) = 0, vbWhite, vbRed))
    End With
    
    With Me.btn_proceed
        .Enabled = IsValid
        If .Enabled Then .SetFocus
        .BackColor = IIf(.Enabled, vbGreen, vbButtonFace)
    End With
End Sub

Private Sub tb_emplnum_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    If KeyAscii >= 48 And KeyAscii <= 57 Then
        'valid
    Else
        Debug.Print "not valid"
        KeyAscii = 0
    End If
End Sub

Private Sub tb_eqtnum_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    If KeyAscii >= 48 And KeyAscii <= 57 Then
        'valid
    Else
        Debug.Print "not valid"
        KeyAscii = 0
    End If
End Sub

Private Sub UserForm_Initialize()

    Set wsPassword = ThisWorkbook.Worksheets("pw")
    
    With Me
        .tb_emplnum.MaxLength = 5
        With .tb_password
            .Enabled = False
            .MaxLength = 16
        End With
        
        With .tb_eqtnum
            .MaxLength = 3
            .Enabled = False
            .Visible = False
        End With
        
        .Label3.Visible = False
        .check1.Visible = False
        .btn_proceed.Enabled = False
    End With
    
End Sub

I have included code that will only allow numeric values in specified controls negating need for msgboxes

Hope Helpful

Dave

 

[Ark68]

Hi Dave, wow. Thank you so much for offering up an alternative solution! It's appreciated.
I'm running into an error with the line in red with attention focused on .SetFocus.

Error: "Can't move focus to the control because it's invisible, not enabled, or of a type that does not accept the focus."

Private Sub tb_emplnum_Change()
    Dim IsValid     As Boolean
    With Me.tb_emplnum
        emplnum = .Value
        m = Application.Match(Val(.Value), ws_pw.Columns(1), 0)
        IsValid = Len(.Value) = .MaxLength And Not IsError(m)
        .BackColor = IIf(IsValid, vbGreen, IIf(Len(.Value) = 0, vbWhite, vbRed))
    End With
    
    With Me.tb_password
        .Enabled = IsValid
        If IsValid Then .SetFocus: .Tag = m
    End With
    
End Sub

 

[Micron]

Did a little research and found that the focus is an issue with beforeupdate cancelling (keyboard focus is lost, logical focus is not). I take logical to mean that in code you could work with the control, just not type into it. Seems the solution would be exit event and pass validation to another sub (which by the way, could be written to handle multiple controls, I think). Then focus should be able to be reset as this is an issue caused by the beforeupdate event.

 

[Ark68]

Did a little research and found that the focus is an issue with beforeupdate cancelling (keyboard focus is lost, logical focus is not). I take logical to mean that in code you could work with the control, just not type into it. Seems the solution would be exit event and pass validation to another sub (which by the way, could be written to handle multiple controls, I think). Then focus should be able to be reset as this is an issue caused by the beforeupdate event.

Thanks for digging deep Micron. Here I came thinking I just made a spelling mistake. Turns out I made things complicated.

 

[dmt32]

Hi,
i did not appreciate that you had changed the buttons visibility manually - change it back as I have set the controls enabled property to false until all controls are complete.

Dave

 

[Micron]

I was going to suggest a simpler validation using exit:

If Not IsNumeric(Me.tb_emplnum) Or Len(Me.tb_emplnum) <> 5 Then
     MsgBox "(01) Please enter a valid 5 digit employee number" & Chr(13) & "{#####}", vbInformation, "ERROR"
     Me.tb_emplnum.SetFocus
End If

If Application.WorksheetFunction.CountIf(Worksheets("pw").Columns(1), emplnum) < 1 Then
        MsgBox "User not permitted.", vbExclamation, "UNAUTHORIZED"
        Me.tb_emplnum.SetFocus
End If

BUT as soon as I tab out the value I put in is erased. I know this from stepping through the code and immediately looking at the form again. Can anyone explain that?

 

[Micron]

Got the answer to that. You bound the control to a cell - why? No can do IMO. Removing that stops the tb contents from always being regarded as "", which as you said, gets fixed on the next pass. Validation shouldn't be a problem if you remove that, and if you want it to act as a bound control but not be one, have your code populate the cell after it passes validation.