Identify which control has focus

[osera]

Hello!

I'm working on a form in Excel. There isn't any set order in which the users have to move from control to control; they can go in whatever order works best for them. However, there is an OnExit event for one drop down box that I would like to change if they go from it to a particular other control.

Is there a way to identify which which control the user has activated so as to run a different OnExit event than the one that would run in all other instances?

Thank you in advance.

 

[osera]

Update:

I found the ActiveControl function which identifies the control that currently has focus. Unfortunately, the focus doesn't shift to the new control until after the OnExit event finishes running.

 

[fumei]

Unfortunately, the focus doesn't shift to the new control until after the OnExit event finishes running. "

It could not really be otherwise, could it? OnExit must finish/terminate before focus shifts.

"Is there a way to identify which which control the user has activated so as to run a different OnExit event than the one that would run in all other instances?"

I am not quite following that. each control has its own OnExit event.

Gerry

 

[MarkWalsh]

Are you trying to determine which control was active *before* the current one? If so, you could set a variable in the OnExit event for each control, and check that variable when the control you want to have a different action gets focus.

 

[osera]

What I was looking for was a way to have an "If....then..." statement in the OnExit function that would run a different set of instructions if the focus was shifting to a specific control than what runs when that control loses focus to any other control.

I think I found a way to do what I want by storing the name of the previous control in a variable. Then having the OnEnter event (of the one control for which I want a different set of instructions run) to check if the previous control is the one I wanted to run a different code when moving to the control with the new OnEnter event.

 

[DaveInIowa]

It sounds as though you're going to need to need to capture the current control on each control's _Enter event. You can then check in your "particular other control's" _Enter event what control they just came from and if it is the drop down box, do your special processing, set a flag, etc.

Private currentControl As Control

Private Sub CommandButton1_Enter()
    Set currentControl = UserForm1.ActiveControl
End Sub

Private Sub CommandButton2_Enter()
    Set currentControl = UserForm1.ActiveControl
End Sub

Private Sub TextBox1_Enter()
    Set currentControl = UserForm1.ActiveControl
End Sub

Private Sub TextBox2_Enter()
    If (currentControl.Name = "CommandButton1") Then
        MsgBox "Can't go to TextBox2 from CommandButton1"
        currentControl.SetFocus
        Exit Sub
    End If
    Set currentControl = UserForm1.ActiveControl
End Sub

 

[osera]

Thank you all for your responses. I have a block of code that is essentially doing what MarkWalsh suggested. It seems to be working for me. Hopefully, none of my users will find a way to screw it up

 

[fumei]

Hopefully, none of my users will find a way to screw it up "

LOL!


Good luck with that!

"What I was looking for was a way to have an "If....then..." statement in the OnExit function that would run a different set of instructions if the focus was shifting to a specific control than what runs when that control loses focus to any other control."

Ooooooo, that would be nice! Unfortunately, VBA is not a predictor of user actions. There is no way for it to know - during the OnExit event - what the next control is going to be. All it knows is that it is exiting THIS one.

True, it is possible to use the TabIndex to know what the next control is in the Tab order...but, VBA has no way of knowing if in fact that IS the next control. The user can click anywhere.

So yes, you are stuck with elaborate use of OnEnter, and flags set by each OnExit.

Gerry