SetFocus not working on TextBox after AfterUpdate

This is driving me crazy. I have googled and found multiple similar threads but none of their solutions work for me. (The most promising was a solution from AndyPope of moving the SetFocus code to UserForm_Activate)

I am creating an Add Contact form. There is a checkbox for Individual (if left unticked then contact is a business entity - not an individual).

If Individual = True and If the txtNameFirst control has no value in it Then after the end user tabs away from the txtNameLastRegistered, the form should SetFocus on the txtNameFirst textbox (entry for this field is mandatory only if the contact is an individual - otherwise this field is made invisible)

But it is not working. I tick chkIndividual, I enter a name in txtNameLastRegistered, I tab away, code is called from txtNameLastRegistered_AfterUpdate (which includes the SetFocus code) but the form appears without focus in any of the controls!

My code below:

Option Explicit

Private mbytEntryStage As Byte
Private mstrLastName As String

Private Sub UserForm_QueryClose(ByRef Cancel As Integer, _
                                ByRef CloseMode As Integer)
    If CloseMode = vbFormControlMenu Then
        Cancel = True
        'exit properly
        Call cmdCancel_Click
    End If
End Sub

Private Sub UserForm_Initialize()
    Call FormUpdateFormControls
End Sub

Private Sub UserForm_Activate()
    With Me
        .StartUpPosition = 0
        .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
        .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
    End With
End Sub

Private Sub chkIndividual_Click()
    Call FormUpdateFormControls
End Sub

Private Sub cmdCancel_Click()
    Unload Me
    'only while testing. change to hide on user version
End Sub

Private Sub txtNameFirst_AfterUpdate()
    Call FormUpdateFormControls
End Sub

Private Sub txtNameLastRegistered_AfterUpdate()

    With Me
        Select Case Len(.txtNameLastRegistered.Value)
        Case 0
            Select Case mbytEntryStage
            Case 0
                'do nothing
                '(this can be triggered by form at stage 0 and user enters name then immediately backspaces to 0 len without tabbing away)
            Case 1
                'restore previous value
                .txtNameLastRegistered.Value = mstrLastName
                'warn user
                MsgBox "INSERT WARNING HERE FOR END USER", vbOKOnly, gstrcDBMS_SysGuiName
            End Select
        Case Is > 0
            'store current value (in case we need to restore if user backspaces to 0 len)
            mstrLastName = .txtNameLastRegistered.Value

            'Form stage is increased to 1
            If mbytEntryStage = 0 Then
                mbytEntryStage = 1
            End If

            Call FormUpdateFormControls
        Case Else
            'impossible
            Debug.Assert False
        End Select
    End With
End Sub

Private Sub FormUpdateFormControls()

    With Me
        .lblNameFirst.visible = .chkIndividual.Value

        Select Case .chkIndividual.Value
        Case True
            .txtNameLastRegistered.Width = 204
        Case False
            .txtNameLastRegistered.Width = 280
            .txtNameFirst.Value = vbNullString
        End Select
    End With

    Select Case mbytEntryStage
    Case 0
        With Me
            .lblNameContact.visible = False
            .cmdOK.Enabled = False
            
            .txtNameCode.visible = False
            .txtNameFirst.visible = False
            .txtAddressLine1.visible = False
            .txtAddressCity.visible = False
            .txtAddressState.visible = False
            .txtAddressZip.visible = False
            .txtAddressCountry.visible = False
            .txtContactPh1.visible = False
            .txtContactPh2.visible = False
            .txtContactFax.visible = False
            .txtContactEmail.visible = False
            .txtContactWeb.visible = False
            .txtNameDear.visible = False
        End With
    Case 1
        With Me
            .lblNameContact.visible = True
            .txtNameCode.visible = True
            .txtAddressLine1.visible = True
            .txtAddressCity.visible = True
            .txtAddressState.visible = True
            .txtAddressZip.visible = True
            .txtAddressCountry.visible = True
            .txtContactPh1.visible = True
            .txtContactPh2.visible = True
            .txtContactFax.visible = True
            .txtContactEmail.visible = True
            .txtContactWeb.visible = True
            .txtNameDear.visible = True

            .txtNameFirst.visible = .chkIndividual.Value

            Select Case Len(.txtNameFirst.Value)
            Case 0
                .lblNameContact.Caption = .txtNameLastRegistered.Value
                .cmdOK.Enabled = Not .chkIndividual.Value

                'prompt user to complete empty mandatory field (individual & first name field is empty)
                If .chkIndividual.Value = True Then
                    .txtNameFirst.SetFocus
                End If

            Case Is > 0
                .lblNameContact.Caption = .txtNameLastRegistered.Value & ", " & .txtNameFirst.Value
                .cmdOK.Enabled = .chkIndividual.Value
            Case Else
                'impossible
                Debug.Assert False
            End Select
        End With
    End Select
End Sub

Setfocus works but after focus is set next control in tab order will receive focus - unless it is hidden or disabled by called code. It is effect of buffered tab keystroke.

Originally Posted by Izandol

Setfocus works but after focus is set next control in tab order will receive focus - unless it is hidden or disabled by called code. It is effect of buffered tab keystroke.

That explains it.

Do you have any thoughts as to how I might be able to work around this?

I have already tested KeyDown and that event fires before AfterUpdate. So if I use KeyDown to negate the Tab key (from the txtNameLastRegistered textbox) then the txtNameLastRegistered_AfterUpdate won't fire.

Do not try to set focus there. You may highlight control - change colour for example - and prevent submission of data while control is in error. Submit button code may be used to set focus to any control in error.

Originally Posted by Izandol

Do not try to set focus there. You may highlight control - change colour for example - and prevent submission of data while control is in error. Submit button code may be used to set focus to any control in error.

But I'm not trying to highlight/prevent an error as such.


I'm trying to prompt the end user to complete the FirstName field by setting focus in that textbox (I want to avoid highlighting) if all of the following conditions are met:

Individual=Yes
FormStage=1 (which is triggered by value entered into LastName)
FirstName field has no entry

To make it easier for some kind forum user to find a solution, I'm uploading a dummy example of the form for testing.

The goal is if all of the following conditions are met:

1. Individual=Yes
2. FormStage=1 (which is triggered by value entered into LastName)
3. First Name field has no entry made

Then the First Name field should have the SetFocus.

What I can't understand is this:

If the user opens the form, enters a Last Name and then clicks on Individual (or tabs away from Last Name and then click on Individual), the First Name appears and it DOES have SetFocus.

So why doesn't the SetFocus work if the user clicks on Individual checkbox BEFORE entering a Last Name?


UPDATE: After experimenting further, I'm convinced Izandol is onto something. If Individual checkbox is clicked first and the user TABS away from Last Name, the SetFocus is lost. If they CLICK into another field instead, the SetFocus is set to First Name like it should.

Tried changing the tab order so that the Contact Code is the next in the tab order after the Last Name (in case switching visibility on the First Name txtbox was causing problems). Didn't work.


In desperation I've tried to clear the Keyboard Buffer using the code in this thread http://www.mrexcel.com/forum/excel-q...rd-buffer.html

I call the function in the txtNameLastRegistered_AfterUpdate but the SetFocus still won't work.

Ha! I've finally worked out a solution. :D

Here's a coding challenge - a rep is waiting for the first forum user to work out how I did this!

Originally Posted by deepika.shinde

Hi plz share the solution

Well OK. But I withdraw the coding challenge now that I'm revealing the answer.

In the end, the work-around was quite simple (if somewhat unorthodox!). All I needed to do was: 1. change the TabIndex order at runtime, 2. SetFocus on any visible control then 3. setfocus on the control that the event is running on. This forces the SetFocus to the next control in the new TabIndex order!

Example below:

Private Sub txtNameLastRegistered_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
'use of Tab on txtNameLastRegistered control will cause SetFocus to be set on
'either txtNameCode or txtNameCode depending on what stage the form is up to

    If KeyCode <> vbKeyTab Then
        'only need this code to fire when Tab is used
        Exit Sub
    End If

    'switch the tabindex order
    On Error Resume Next
    With Me
        .txtNameCode.TabIndex = 5
        .txtNameFirst.TabIndex = 6

        If mbytEntryStage = 1 And .chkIndividual.Value = True Then
            If Len(Trim(.txtNameFirst.Value)) = 0 Then
                'we want txtNameFirst to have set focus, not txtNameCode
                .txtNameFirst.TabIndex = 5
                .txtNameCode.TabIndex = 6
            End If
        End If

        'need to setfocus on any other visible control BEFORE setfocus on this control
        .cboContactType.SetFocus

        'setfocus on the control running this event code
        .txtNameLastRegistered.SetFocus
    End With
    On Error GoTo 0
End Sub

You can overcome the problem with a pair of subs. In this example of a working pair of scripts, I have a time card entry form.
TextBox1 is the hours in. TextBox2 is the minutes in. Because of idiots entering incorrect values, like spaces, or fat-fingering
numbers, I had to write a validation. But I wanted it to clear the invalid text and set the focus back into the textbox where
the invalid text had been entered. The form functioned with the same problem as noted in this thread. When the hours were entered
incorrectly, it would clear the text, display a message box, and it was supposed to set the focus back on the hours box.
Unfortunately, the buffered tab would make it go to the minutes box.

So to correct this problem, I disabled TextBox2, which was the next tab index position. That prevented the focus from going there.
Then I cleared the text value from TextBox1, to eliminate the invalid text.
Next I set the focus back to TextBox1.

Lastly, I did a separate sub routine to reenable TextBox2 once the sub routine of clearing and setting focus on TextBox1 was complete.

The end result is what you are asking about. The two routines are below. And the order of operations matters. You have to clear the value
before setting the focus. Otherwise you end up in a loop; an escapable loop, but a loop nonetheless.

------------------

Private Sub TextBox1_Enter()
TextBox2.Enabled = True
End Sub

------------------

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox1.Value <> "" Then
If IsNumeric(TextBox1.Value) = False Or TextBox1.Value < 1 Or TextBox1.Value > 23 Then
    MsgBox Chr(34) & TextBox1.Value & Chr(34) & " is not a valid number for the hour."
    TextBox2.Enabled = False
    TextBox1.Value = ""
    TextBox1.SetFocus
Else
    'Do other stuff that is not relevant to the topic of the thread.
End If
End If

End Sub

------------------

Works beautifully. Hope it helps, whoever has need of it.

alex, welcome to the forum

Thanks for the contribution, Im sure members will find it useful

(did you notice that this thread is over 2 years old though?)

Zep thanks for the feedback