Userform "Reset" - Set user focus on a particular control, contents highlighted, ready for user to overwrite

[Ark68]

I have some userform code that checks the validity of data submitted by a user in a textbox. Should the date be invalid, the user is advised, and the userform is reset (ie. the defualt values of certain controls are repopulated). When the userform is recreated, I wish for the focus to be put on a particular textbox (lat_dd), highlighted, and ready for the user to over write the default (ie avoid the user from having to click or tab into the textbox). I have this code in an attempt to do so. No errors, but not providing the desired results. The next TabIndex control is selected (lat_dd is TabIndex 6, but following this code execution TabIndex 7 - long_dd, is selected and it's content's highlighted).

In my experimentation below, I read that my desired result could be achieved by switching focus from one control back to the preferred one first, but this doesn't seem to work.

Any suggestions on how to best improve my code to produce the desired effect?

Private Sub lat_dd_AfterUpdate()
    Dim val_len As Double
    mbevents = True 'remove outside of testing, it is set to tru in module 1
    If Not mbevents Then Exit Sub
    If IsNumeric(lat_dd.Value) Then
        lat_dd.Value = Format(lat_dd.Value, "00.000000")
        If lat_dd.Value > 90 Then
            MsgBox "Invalid latitude." & Chr(13) & Chr(13) & "Latitude range 0 - 90 degrees.", , "Error: Exceeds maximum"
            mbevents = False
            coordinates_reset
            long_dd.SetFocus
            lat_dd.SetFocus
            mbevents = True
            Exit Sub
        ElseIf lat_dd.Value < 0 Then
            MsgBox "Invalid latitude." & Chr(13) & Chr(13) & "Latitude range 0 - 90 degrees.", , "Error: Limited to north hemisphere"
            mbevents = False
            coordinates_reset
            long_dd.SetFocus
            lat_dd.SetFocus
            mbevents = True
            Exit Sub
        End If
    Else
        MsgBox "Invalid latitude.", , "Error: Numbers Only"
        mbevents = False 'don't trigger events
        lat_dd.Value = "00.000000"
        mbevents = False
        coordinates_reset
        long_dd.SetFocus
        lat_dd.SetFocus
        mbevents = True
        Exit Sub
    End If
...

Note that I have this sub as well, that I use (without any real noticeable effect as the user still needs to clickj the control to get focus) when the form is initialized to put the user in the right control to overwrite ... similar to what I'm trying to achieve with a reset). I tried putting this sub in replacing the setfocus commands in the code provided above, but it still behaved identically.

Private Sub lat_dd_Enter()
    lat_dd.SelStart = 0
    lat_dd.SelLength = Len(lat_dd.Text)
End Sub

 

[jkpieterse]

The AfterUpdate event often prevents one from changing the focus. Try using a different control event.

 

[Jaafar Tribak]

Try this and see how it goes :

Option Explicit

Private bCancelExit As Boolean

Private Sub lat_dd_AfterUpdate()
    Dim val_len As Double
    mbevents = True 'remove outside of testing, it is set to tru in module 1
    If Not mbevents Then Exit Sub
    If IsNumeric(lat_dd.Value) Then
        lat_dd.Value = Format(lat_dd.Value, "00.000000")
        If lat_dd.Value > 90 Then
            MsgBox "Invalid latitude." & Chr(13) & Chr(13) & "Latitude range 0 - 90 degrees.", , "Error: Exceeds maximum"
            mbevents = False
            Call Reset
            Exit Sub
        ElseIf lat_dd.Value < 0 Then
            MsgBox "Invalid latitude." & Chr(13) & Chr(13) & "Latitude range 0 - 90 degrees.", , "Error: Limited to north hemisphere"
            mbevents = False
            Call Reset
            Exit Sub
        End If
    Else
        MsgBox "Invalid latitude.", , "Error: Numbers Only"
        mbevents = False 'don't trigger events
        lat_dd.Value = "00.000000"
        Call Reset
        Exit Sub
    End If
End Sub
 
Private Sub Reset()
    coordinates_reset
    'long_dd.SetFocus
   ' lat_dd.SetFocus
    mbevents = True
    bCancelExit = True
End Sub

Private Sub lat_dd_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Cancel = bCancelExit
    bCancelExit = False
End Sub

Private Sub lat_dd_Enter()
    lat_dd.SelStart = 0
    lat_dd.SelLength = Len(lat_dd.Text)
End Sub