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
Bookmarks