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

jardenp

Active Member
Joined
May 12, 2009
Messages
373
Office Version
  1. 2019
  2. 2016
  3. 2013
  4. 2011
  5. 2010
Platform
  1. Windows
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.
VBA Code:
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!
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Hi,
try using the KeyDown event & see if this will do what you want

VBA Code:
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
 
Upvote 0
Solution
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:
VBA 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
 
Upvote 0
Hi,
glad suggestion resolved your issue - appreciate feedback

Dave
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,322
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top