VBA-Userforms: how to set focus in an exit event (or some other even if it works the same)

[jardenp]

I would like to set the focus on the CheckTrip_CB1 command button text box MilesPaid_TB1 is empty to avoid the user having to tab through about 10 fields that are irrelevant because that certain condition is met.

When I run this code, I get runtime error '-2147467259 (80004005)' unspecified and clicking debug takes me to the .SetFocus line.

Private Sub EndOdom_TB1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Entry = 1
    ExitAllSubs = False
    EndOdomExit
    Entry = 0
    If Me.MilesPaid_TB1 = "" Then
        Me.CheckTrip_CB1.SetFocus
    End If
    Cancel = True
    
End Sub

I've read through 25-30 posts on how to get .SetFocus to work here and I can't make sense of it. I've seen multiple "workaround" solutions but none work for me.

Everything in the sub EndOdomExit works fine. Everything works fine unless I try to set the focus in the exit event sub.

When I put a debug.print or msgbox immediately above the .SetFocus line, it seems it's running through the code a couple times before hitting the error.

Has anyone figured run into this issue and found a satisfactory answer? Thanks!

 

[dmt32]

Hi,
try using the KeyDown event & see if this will do what you want

Private Sub EndOdom_TB1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = vbKeyTab And Len(Me.MilesPaid_TB1.Text) = 0 Then Me.CheckTrip_CB1.SetFocus
End Sub

Dave

 

[jardenp]

Thank you, dmt32! That works! I was able to add my other code and it all seems to work just fine. For posterity's sake, here's the final code:

Private Sub EndOdom_TB1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = vbKeyTab Then
        Entry = 1
        ExitAllSubs = False
        EndOdomExit
        Entry = 0
        If Len(Me.MilesPaid_TB1.Text) = 0 Then
            Me.CheckTrip_CB1.SetFocus
        End If
    End If
    
End Sub

 

[dmt32]

Hi,
glad suggestion resolved your issue - appreciate feedback

Dave