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
369
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

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,663
Office Version
  1. 2019
Platform
  1. Windows
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
 
Solution

jardenp

Active Member
Joined
May 12, 2009
Messages
369
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
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,663
Office Version
  1. 2019
Platform
  1. Windows
Hi,
glad suggestion resolved your issue - appreciate feedback

Dave
 

Forum statistics

Threads
1,136,796
Messages
5,677,785
Members
419,720
Latest member
kurman

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
Top