Results 1 to 20 of 20

SetFocus not working on TextBox after AfterUpdate

Threaded View

  1. #1
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    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
    Last edited by mc84excel; 03-30-2014 at 07:49 PM. Reason: correct grammar
    *******************************************************

    HELP WANTED! (Links to Forum threads)
    Trying to create reusable code for Custom Events at Workbook (not Application) level

    *******************************************************

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VBA Setfocus to next textbox
    By pjbassdc in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-07-2013, 12:47 PM
  2. AfterUpdate/BeforeUpdate and SetFocus
    By odekkers in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-05-2010, 03:32 PM
  3. AfterUpdate event for dynamic TextBox
    By moyo in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-20-2007, 02:23 AM
  4. TextBox.SetFocus not working
    By vesoljc in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 06-11-2007, 01:49 AM
  5. Form, SetFocus and AfterUpdate issue
    By Piers 2k in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-08-2005, 09:55 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1