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.

 

[Dan_W]

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.

Are we all using the Textbox as the "ActiveControl"? (I am). Might that issue somehow be related to the fact that the VBA textbox is not a 'real' textbox?

 

[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

Separately, I can say this in relation to any/every part of VBA to be honest (... sigh)

 

[mikerickson]

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.

Have you tried a Userform Deactivate event that changes the Tab order so that the Active Control is at the top?

 

[Jaafar Tribak]

What control did it set it to?

It sets the focus to the first control (tabindex 0) that is located inside the frame.

I will give you a minimal example to reproduce the issue.

Suppose you have a userform with a textbox place on it and a frame . Inside the frame you also have a textbox.

Now, before I leave the form, the textbox that is placed on the from is the one that has last the focus.

Now, when I return to the form by clicking inside the frame, the focus is incorrectly set to the textbox that is inside the frame not to the textbox that is placed on the form. However, if I return to the form by clicking on the form (or on its titlebar), the focus correctly gos back to the textbox on the form.

 

[Jaafar Tribak]

Have you tried a Userform Deactivate event that changes the Tab order so that the Active Control is at the top?

Thanks Mike.

The deactivate event doesn't fire when leaving the userform unless we are activating another userform.
Also, messing with the tab orders could quickly get messy. I am trying to avoid that.

 

[Anthony47]

I got a decent result using the deprecated SendKeys

Assuming that Userform_Click is used to re-set focus on Userform1, then try putting these two lines in the UserForm_Click macro

SendKeys "+{TAB}"
SendKeys "{TAB}"

This worked "in most of the cases"
One of the confusing situations arise when the focus is removed from the userform for example activating a different application, or even clicking on the title bar of Excel. In this case when we return to Excel the userform looks in focus but which the selected control is unknown, unless we Click on the form (or use Tab+ /Tab-)

Another approach that I tried, ie logging the "control_enter" sequence, was much more painful in terms of coding but less usefull, because the Controls in a Multipage trigger the "Control_Enter" event only on the first instance then no longer.

 

[Jaafar Tribak]

@Anthony47

In fact, I had already tried that method (Actually, I used mouse_event api which has similar effect but whithout the side effect of turning the NumLock key On and Off)

The SendKeys method works and it does so with little to almost no code. This is good but it comes with the following issues that I am trying to avoid :

1- It triggers the current and previous controls Enter\Exit events which is undesired as there may be code firing there.
2- Obviously, like you said, this assumes Userform_Click is used. If the useform is activated by clicking the titlebar, it won't work.
3- Say you have Textbox1 placed on the userform and Textbox2 placed inside a frame. If you leave the userform with the focus set on Textbox1 and then when you come back and click an empty area inside the frame, the focus is set to TextBox2. Only if you click the userform pane (empty area) , Textbox1 gets the focus.

Regarding point (3), one may argue that this is the default behaviour when clicking inside a frame but I am willing to set the focus back to the control that was last active before leaving the userform (ie:= Textbox1) regardless of where the user clicks unless the click is performed on an actual control.

I am in the process of writing some code. It is not perfect but quite close to what I am looking for. I will post it later when done.

Thank you Anthony for your perseverance and willingness to help.

 

[Jaafar Tribak]

Ok, here is what I have ended up with. I have wrapped the worker code in a class module to mimic a Form_OnFocus event.

The code seems to work ok when tested on controls placed on the userform as well as inside frames... It captures clicks everywhere including clicks on the form titlebar and doesn't fire the Enter or Exit events. However, It doesn't work with Multipages.

Unfortunately, I had to resort to the windows api... I guess, a little too much code for not much in return but it was a good learning exercise.

UserFormFocus.xlsm


1- Class Module (CFocusEvent)

Option Explicit

Public Event OnFocus(ByVal ActiveCtrl As MSForms.Control)

Private WithEvents FocusMonitoringRoutine As CommandBars

Private Type POINTAPI
    X As Long
    Y As Long
End Type

#If VBA7 Then
    #If Win64 Then
        Private Declare PtrSafe Function WindowFromPoint Lib "user32" (ByVal Point As LongPtr) As LongPtr
        Private Declare PtrSafe Function AccessibleObjectFromPoint Lib "oleacc" (ByVal arg1 As LongLong, ppacc As Any, pvarChild As Variant) As Long
        Private Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As LongLong)
    #Else
        Private Declare PtrSafe Function WindowFromPoint Lib "user32" (ByVal xPoint As Long, ByVal yPoint As Long) As LongPtr
        Private Declare PtrSafe Function AccessibleObjectFromPoint Lib "oleacc" (ByVal lX As Long, ByVal lY As Long, ppacc As IAccessible, pvarChild As Variant) As Long
    #End If
    Private Declare PtrSafe Function IUnknown_GetWindow Lib "shlwapi" Alias "#172" (ByVal pIUnk As IUnknown, ByVal hUF As LongPtr) As Long
    Private Declare PtrSafe Function GetCursorPos Lib "user32.dll" (lpPoint As POINTAPI) As Long
    Private Declare PtrSafe Function GetActiveWindow Lib "user32" () As LongPtr
    Private Declare PtrSafe Function GetAncestor Lib "user32" (ByVal hUF As LongPtr, ByVal gaFlags As Long) As LongPtr
    Private Declare PtrSafe Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As Integer
#Else
    Private Enum LongPtr
        [_]
    End Enum
    Private Declare Function WindowFromPoint Lib "user32" (ByVal xPoint As Long, ByVal yPoint As Long) As LongPtr
    Private Declare Function AccessibleObjectFromPoint Lib "oleacc" (ByVal lX As Long, ByVal lY As Long, ppacc As IAccessible, pvarChild As Variant) As Long
    Private Declare Function IUnknown_GetWindow Lib "shlwapi" Alias "#172" (ByVal pIUnk As IUnknown, ByVal hUF As LongPtr) As Long
    Private Declare Function GetCursorPos Lib "user32.dll" (lpPoint As POINTAPI) As Long
    Private Declare Function GetActiveWindow Lib "user32" () As LongPtr
    Private Declare Function GetAncestor Lib "user32" (ByVal hUF As LongPtr, ByVal gaFlags As Long) As LongPtr
    Private Declare Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As Integer
#End If

Private oForm As UserForm


' ______________________________________ PUBLIC METHODS ___________________________________

Public Sub BeginMonitoring(ByVal UForm As UserForm)
    Set oForm = UForm
    Set FocusMonitoringRoutine = Application.CommandBars
    Call FocusMonitoringRoutine_OnUpdate
End Sub

Public Sub ForceFocus(ByVal Ctrl As MSForms.Control)
    On Error Resume Next
    With Ctrl
        .Visible = False
        .Visible = True
        .SetFocus
    End With
End Sub


' ______________________________________ PRIVATE ROUTINES ___________________________________

Private Sub FocusMonitoringRoutine_OnUpdate()

    Const S_OK = 0&
    Const CHILDID_SELF = 0&
    Const GA_ROOT = 2&
    Const ROLE_SYSTEM_GROUPING = &H14&

    Static bFormActivated As Boolean
    Static oPrevActiveCtrl As Object

    Dim hwnd As LongPtr
    Dim oIAcc As IAccessible, lRole As Long
    Dim tCurPos As POINTAPI

    Call GetCursorPos(tCurPos)
    #If Win64 Then
        Dim lPtr As LongLong, hWinUnderMouse As LongLong
        Call CopyMemory(lPtr, tCurPos, LenB(tCurPos))
        hWinUnderMouse = WindowFromPoint(lPtr)
        Call CopyMemory(lPtr, tCurPos, LenB(lPtr))
        If AccessibleObjectFromPoint(lPtr, oIAcc, CHILDID_SELF) = S_OK Then
    #Else
        Dim hWinUnderMouse As Long
        hWinUnderMouse = WindowFromPoint(tCurPos.X, tCurPos.Y)
        If AccessibleObjectFromPoint(tCurPos.X, tCurPos.Y, oIAcc, CHILDID_SELF) = S_OK Then
    #End If
        lRole = oIAcc.accRole(CHILDID_SELF)
    End If

    Call IUnknown_GetWindow(oForm, VarPtr(hwnd))

    If GetActiveWindow = hwnd Then
        If bFormActivated = False Then
            bFormActivated = True
            If lRole = ROLE_SYSTEM_GROUPING Then
                RaiseEvent OnFocus(oPrevActiveCtrl)
            Else
                RaiseEvent OnFocus(RealActiveControl)
            End If
            SetTabStops True
        End If
    Else
        If bFormActivated Then
            bFormActivated = False
            Set oPrevActiveCtrl = RealActiveControl
            SetTabStops False
        End If
    End If

    If GetAncestor(hWinUnderMouse, GA_ROOT) <> hwnd And GetAsyncKeyState(VBA.vbKeyLButton) Then
        bFormActivated = False
        Set oPrevActiveCtrl = RealActiveControl
        SetTabStops False
    End If

    With Application.CommandBars.FindControl(ID:=2040)
        .Enabled = Not .Enabled
    End With

End Sub

Private Sub SetTabStops(ByVal bStop As Boolean)
    Dim oCtrl As MSForms.Control
    For Each oCtrl In oForm.Controls
        oCtrl.TabStop = bStop
    Next oCtrl
End Sub

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

2- Code Usage Example (UsrForm Module)

Option Explicit

Private WithEvents Form As CFocusEvent

Private Sub UserForm_Initialize()
    Set Form = New CFocusEvent
    Form.BeginMonitoring Me
End Sub

' _______________________________________PSEUDO EVENT ________________________________________

Private Sub Form_OnFocus(ByVal ActiveCtrl As MSForms.Control)
    Form.ForceFocus ActiveCtrl
End Sub

 

[Anthony47]

Thank you Anthony for your perseverance and willingness to help

To be honest, I am not here (on this forum) to help, but rather to learn: there is nothing but practice that force you to learn, and some of the questions I read here are really challenging...
Unfortunately, the latest published code is out of my reach

 

[Jaafar Tribak]

To be honest, I am not here (on this forum) to help, but rather to learn: there is nothing but practice that force you to learn, and some of the questions I read here are really challenging...
Unfortunately, the latest published code is out of my reach

Exactly the same here ... By helping aswering questions and solving problems, we too learn.