Setting Back Focus To ActiveControl ?!

[Jaafar Tribak]

This is a known annoyance which doesn't seem to be difficult to address at first glance but, after a couple of tries, I am still unable to figure out a fix. I have searched the forum and googled a bit to see if this was addressed before. .. I have even tried a couple of win api methods but with no success so far.

This is the situation : I have a simple Modeless UserForm with a few controls placed on it ... I click out of the userform and select a worksheet cell. Then, when done working with the worksheet, I click back on the userform and I expect to see the keyboard focus back on the last activecontrol (ie: the control that was last active when deactivating\leaving the userform ) without the need for the user to perform an extra click ... Annoyingly, the keyboard focus never gets set into the last active control.

The Userform doesn't offer a GetFocus Event so, it is not that easy to intercept the moment the user gets back to the userform ... I hope there is already a simple fix\workaround to this so I can stop digging further.

Any thoughts ? Thanks.

 

[Anthony47]

My suggestion is probably too naive, but it worked fine when I tested it out in a very simple situation (few textboxes and 1 commandbutton).

This should re-set the focus on the latest active control:

Private Sub UserForm_Click()
Dim cTab As Long
'
cTab = Me.ActiveControl.TabIndex
If cTab = 1 Then
    Me.Controls(2).SetFocus
Else
    Me.Controls(1).SetFocus
End If
Me.Controls(cTab).SetFocus
End Sub

Don't ask why symply using Me.ActiveControl.SetFocus don't work

This will move focus to next control:

Private Sub UserForm_Click()
Dim cTab As Long
'
Me.ActiveControl.SetFocus
cTab = Me.ActiveControl.TabIndex
Me.Controls(cTab + 1).SetFocus
End Sub

Of course these are prototypes, the code need to be completed to cope with possible error situations

This assumes you CLICK on the form to give back focus; it will not work if you click on the form header. For this, maybe (maybe) hiding the form header could be a reasonable bypass

 

[Jaafar Tribak]

Thank you very much Anthony47

This assumes you CLICK on the form to give back focus; it will not work if you click on the form header.

Exactly. and that's one of the things I want to avoid. Also, the situation gets even more complicated when you have controls nested inside frames.

 

[Anthony47]

I agree that the trick works only on the simplest situations.
I don't think I will get any brilliant idrea; the only thing I can think of is creating a log with the activation sequence and replay the last activation when the form is re-selected. This is in case the result is worth the effort.
Hope that some of the xl gurus come into the discussion...

 

[Jaafar Tribak]

he only thing I can think of is creating a log with the activation sequence and replay the last activation when the form is re-selected

That would work if only there was some sort of OnFocus\OnSelect event but there isn't ... The form activate event doesn't fire when going back from the worksheet to the form.

Thanks for your interest in this Anthony.

 

[Dan_W]

I've tried but haven't come up with anything useful, I'm afraid. To be honest, I'm not sure I knew that ActiveControl.SetFocus was so unhelpful until I saw this post.

Prompted by Anthony's suggestion about using Me.Controls(cTab).SetFocus (that had never occured to me), I tried this in the MouseMove event of the Userform. I concede it wouldn't be perfect, especially in a userform covered in controls and frames, etc, but I find that it tends to trigger at least once in most UserForm designs. Importantly, it does trigger in a modeless UserForm even when the UserForm doesn't have focus, so I thought that this might solve the "avoiding having to click the userform" requirement. But (perhaps unsurprisingly to everyone else), it still didn't work, though that could be because I did something wrong with the code.

I'm still thinking about it, but my mind is veering off into APIs, and that falls foul of the "simple solution" point.

 

[Jaafar Tribak]

@Dan_W

I spent many hours trying to tackle this from different angles (inc resorting to api calls) but the results I got so far are inconsistent specially when having controls placed in nested frames.

If we ever come up with a decent workaround, we should post it here for the benefit of others.

Thanks.

 

[Anthony47]

I had also tested the MouseMove event to return focus to the form
I saw two problems with this:
1- highly posssible unwanted refocus while moving the mouse over the worksheet and "touching" the form
2- the full code (to restore the form to the condition it was left) need to be executed only once, and thus the need for a firm information about who has currently the focus; but I think this is a slow process, too slow to be repeated each time the mouse moves (or vibrates). Using a boolean flag to store this status cannot guarantee a reliable result: I agree that we might use a Selection_Change for setting the boolean flag to "Focus on the Sheet", but for example, if you set focus to the worksheet by clicking on the cell that is already active and then return immediately to the form then we cannot know the we are returning to the form. Personally I don't like a method that behave in a certain way in "most of the cases".

Anyway, to fulfill what JT was looking for there is still the need for a log procedure that store which control get selected on the form and select it again when returning focus to the form, a procedure that might be different for each of the controls.

And finally I don't think that JT "annoyance" is due to the need for clicking (we are accustomed to click and mark our choice since Doug Engelbart invented the mouse), but rather on why someone decided that a modeless form has to chache its status on loosing focus...

Edit: ready to press Submit when JT published his message...

 

[Jaafar Tribak]

@Anthony47

I had written this little function to return the actual active control that currently has the focus irrespective of where the control is located, ie: inside a frame or on the form. The function works for returning the activecontrol when ran in a loop but , when getting back to the form by clicking inside a child frame, the form incorrectly sets the focus to the wrong control.

Private Function RealActiveControl() As MSForms.Control
    Dim oControl As MSForms.Control
 
    On Error Resume Next
    Set oControl = oForm.ActiveControl
    Do
        Set oControl = CallByName(oControl, "ActiveControl", VbGet)
    Loop Until TypeName(oControl) <> "Frame"
    Set RealActiveControl = oControl
End Function

I have also experimented with the GetCaretPos and GetCursorInfo apis. These calls are useful and work but again, for some reason, the form doesn't consistently set the focus to the correct control.

I agree that we might use a Selection_Change for setting the boolean flag to "Focus on the Sheet",

That would assume the user selects a cell when leaving the form. In theroy that is not always necessarly the case as the user can simply select some other UI element such as the ribbon, the application titlebar, another form or another application... I wanted to make this bulletproof.

This is turning out to be quite challenging. It is more an annoyance than anything but find it intriguing.

PS: Just a thought. One thing that you may want to try in order to brute-force the focus on a control is to toggle its Enabled Property bedore calling the SetFocus Method.

 

[Dan_W]

The function works for returning the activecontrol when ran in a loop but , when getting back to the form by clicking inside a child frame, the form incorrectly sets the focus to the wrong control.

What control did it set it to?