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]

If the number is wrong the following raises the message box and sets the focus back to empl number. If its good, it sets focus to the button. Not sure if that is exactly the goal, but at least validation is working. Remember, the controls can't be bound to anything that's going to be null or zls ("") at the start.

Private Sub tb_emplnum_Exit(ByVal Cancel As MSForms.ReturnBoolean)

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), Me.tb_emplnum) < 1 Then
    MsgBox "User not permitted.", vbExclamation, "UNAUTHORIZED"
    Cancel = True
    Me.tb_emplnum.SetFocus
End If

End Sub

EDIT - forgot that I had switched to using Me. not emplnum variable. That needs to be put back but my edit time will run out first.
Some may disagree, but IMO you should have Option Explicit in every module by default. It's an editor option.

 

[Ark68]

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

Hi Dave,
I have ensured that all the properties for the controls are enabled. (Enabled = True), and all visible settings = true. No fields are hidden or disabled to my knowledge. However, I am still receiving this error.

When I hover over "IsValid", it responds with true. If I type in the immediate box "?tb_password.enabled" it returns false. Shouldn't it be true based on the statement ".enabled = IsValid"?

 

[dmt32]

Hi,
not sure why you are having issues but change that section of code for following & see if resolves

  With Me.btn_proceed
         .Visible = True
         .Enabled = IsValid
        If .Enabled Then .SetFocus
        .BackColor = IIf(IsValid, vbGreen, vbButtonFace)
    End With

Dave

 

[Ark68]

Hi Dave, thank you for your continued support.
I - well actually Micron - revealed the culprit (thanks Micron!) of the error in post #20. I had the employee number bound to a cell on one of my worksheets. That was something that I forget I changed and failed to change back. With that cell reference removed, all is working wonderfully, even without the substituted code being needed.

Thank you all for your insights and learning opportunities.