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]

Often, such problems arise because the wrong events are used for the flow. For 1) try AfterUpdate event. If this was Access I'd say you probably need the Text property and not Value property for your event. However I don't know if that applies to Excel. For 2) If you're saying there is a problem with a simple tab and being able to type, make sure labels are not getting the focus in spite of your property settings. In Access I'd use the immediate window and inquire ?Screen.ActiveControl and hit enter and expect to get its name. If this is about issues after invalid data entry, I might suggest BeforeUpdate instead since it is able to be cancelled thus focus should stay and data entry should be possible without clicking. Not 100% sure though.

Beyond that I think I'd have to have a file to play with to see what's going on in your form if none of that helps.

 

[Ark68]

Hi Micron, thank you for your reply.
I've provided access to my file for you and all that want to try and see where I'm going wrong.

When you open the file, the workbook open event will take you to sheet "MapMe1" which is blank and protected. The userform "uf_login" will appear. This form represents a login interface for the user. For testing, please use 91919 as the employee number. The entry is checked to ensure a 5 digit number was entered. Anything but is rejected with the user expected to retry.

Once the employee number is entered, [TAB] should send the user to the password textbox. (The password control is disabled until a proper employee number is entered). The password for testing is hello!2022 . Once entered, it is checked to ensure it's valid (ie longer than one character, less than 16) and that it matches the employee number. If wrong, the user is expected to retry the password.

With what was provided, you should first experience the inability to tab out of the employee number field after entering the number. This is because the {SUBMIT} is disabled. If you go into the code and comment out that line to keep the button enabled, you will be able to tab. Save and restart to execute the workbook open event. I would prefer the submit button to be disabled until the user has provided all the required data.

Using the 'afterupdate' event for the employee number textbox seems to have resolved issue #1. Now, re-enter 91919 in the Employee number box, and TAB out. First tab will take you to the submit button. Another tab will take you back to employee number. The next tab takes you to the password control. This is not the preferred sequence. Should be password after employee number, and submit after password. (Actually, there is another control revealed - equipment number - if the password is correct. It should tab here after password, then submit).

Also, part of problem #2, enter one of the values wrong in either the employee number or password boxes. I would like the cursor to return to that box to permit the user to start typing without having to click in the field to activate it. That doesn't happen. To get the cursor, the user has to click the control.

Here is the file.

 

[Micron]

I'll take a look but may not be until tomorrow. Tonight I plan to watch the Astros lose again. Tomorrow I must get some of the leaves bagged. I've got a deep carpet of them & the neighbours probably won't like it if they blow onto their properties. However, download attempt says I need to ask for access permission.

 

[Ark68]

I'll take a look but may not be until tomorrow. Tonight I plan to watch the Astros lose again. Tomorrow I must get some of the leaves bagged. I've got a deep carpet of them & the neighbours probably won't like it if they blow onto their properties.

No worries at all. Putting this to bed for the evening myself. My only break from it today was the short time I was out ... bagging leaves! Enjoy.
Welcome all input!

 

[Ark68]

An update ... the 'AfterUpdate' does not appear to resolve the issue #1 in my working file. The file I have shared with this community shares the exact same code, the only difference between it and my working file is the removal of 4 sheets of sensistive data which for which have no reference in the login code. You may or may not experience this as the MrExcel file does not exhibit this behaviour for me either.

My working file (identical code) still requires the user to enter the same information a second time (for both tb_emplnum and tb_eqtnum ... both fields that can only be numeric), even when the information was identical to what was entered originally. I've updated the original file to include the handling of the "tb_eqtnum' control. You will notice by entering a three digit number that it isn't accepted (throws an error becuase of length?).
Updated file.

 

[Micron]

you probably didn't see my added comment in my edited post.
Permission to download not granted.

 

[Ark68]

Correct. I didn't see that.
Try it now.

 

[RoryA]

I don't have any issue entering the correct user id the first time.

As regards your tabbing issues, it is not a good idea to enable/disable controls as you are tabbing through - it won't work as you want. If you want to validate a control, you should generally be using beforeupdate really since that provides a Cancel argument that you can set to true to simply keep focus on that control if the entry is invalid.

 

[Ark68]

Thank you Rory.
I may have to share my working copy heavily redacted of sensitive information in an effort to recreate the issue in #1. As I mentioned, the file I shared doesn't exhibit the same issue as my working file despite the code being identical. Only the sheet structure differs.

As far as your second piece of advice. I am interpreting it as ... get rid of the disengage/engage function, and use BeforeUpdate with the Cancel parameter? The first part I can do, but I'm too unfamiliar with how to integrate Cancel into the code to make use of it in this scenario. I've referenced several Google finds on Cancel but haven't seen how it would work in my situation.