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.

 

[Jaafar Tribak]

Unfortunately, the latest published code is out of my reach

Sorry, I have this lazy habit of not properly commenting the code.

Basically, what the code does is run the Commandbars OnUpdate event so that it continiously monitors which control is currently active. The OnUpdate event also detects when the form is being deactivated or activated and acts accordingly to store the last active control or to set back the focus.

The CommandBars OnUpdate event serves the same purpose here as that of a loop or a timer albeit less accurate but with much less impact on performance and stability.

WindowFromPoint, AccessibleObjectFromPoint , GetCursorPos and GetActiveWindow are just helper api functions for determining where the user is placing the mouse pointer, which UI element is under the mouse or when the form is being activated\deactivated.

Regards.

 

[Dan_W]

Sorry. Have had a busy week at work and my personal computer self destructed, so it's been Interesting. Apparently the version of office on my old laptop downgraded itself to 32bit Office... so, I guess we will see how strict I've been with making sure that ive done the conditional compilation declarations properly...

 

[davsy]

Hi @Jaafar Tribak Thanks so much for sharing. It's great but there's still issue with Multipages.
For Multipages, it will only set focus to the Page Tab but not on the textbox.
Me too have been trying to find a workaround with multipages. Do you have any idea?

 

[Jaafar Tribak]

Hi @Jaafar Tribak Thanks so much for sharing. It's great but there's still issue with Multipages.
For Multipages, it will only set focus to the Page Tab but not on the textbox.
Me too have been trying to find a workaround with multipages. Do you have any idea?

Unfortunately, the multupage focus is harder to monitor. I haven't found a workaround yet.

 

[davsy]

@Jaafar Tribak I have actually found a workaround based on what i have found here.

[SOLVED] vba to keep the focus on userform last used textbox [Archive] - VBA Express Forum

Hi All, I have created a userform. My team would copy data from other applications and paste it in different text boxes on userform. Whenever they use ALt+Tab to toggle between different other excel sheets or application and come back to userform the focus is not staying in userform. it...

www.vbaexpress.com

I have modified it to add this following IF statement to detect multipage.
Controls in Multipages can be detect with Select Case TypeName(ctl).
But Controls in Frames within the Multipages still need more code to be added but I'm sure it can be done.

If TypeName(ctl) = "MultiPage" Then Set ctl = ctl.Pages(ctl.Value).ActiveControl

You might want to see if u can use this to update your code?

 

[davsy]

@Jaafar Tribak

I have found a way to update your Function to include Multipages. But I am still figuring a way to identify to total number of Multipages to create a loop.

This fuction only works with a parent Multipage.

Private Function RealActiveControl() As MSForms.Control
    Dim oControl As MSForms.Control, oControl2 As MSForms.Control
    On Error Resume Next
    Set oControl = oForm.ActiveControl
    Set oControl2 = oForm.ActiveControl
       
    If TypeName(oControl) = "MultiPage" Then    '''' This only works for parent Multipage
    Set oControl = oControl.Pages(oControl.Value).ActiveControl
    Do
        Set oControl = CallByName(oControl, "ActiveControl", VbGet)
        Loop Until TypeName(oControl) <> "Multipage"
    Set oControl2 = oControl.Pages(oControl.Value).ActiveControl
    Do
        Loop Until TypeName(oControl2) <> "Multipage"
        
        Set RealActiveControl = oControl2
    Else
    
    Do
        Set oControl = CallByName(oControl, "ActiveControl", VbGet)
    Loop Until TypeName(oControl) <> "Frame"
    
    Set RealActiveControl = oControl
    
    End If

This function will allow us to detect the 1 child Multipage within the parent Multipage.
But while it detects the child, it does not detect the parent.

Still looking for a way to detect total number of Multipages so we can loop through all the Mulitpages and Controls to detect the child Multipage within the parent.
Do you think you can find a way for this?

Private Function RealActiveControl() As MSForms.Control
    Dim oControl As MSForms.Control, oControl2 As MSForms.Control
    On Error Resume Next
    Set oControl = oForm.ActiveControl       
    If TypeName(oControl) = "MultiPage" Then  '''to select Multipage    
    Set oControl = oControl.Pages(oControl.Value).ActiveControl
    Do
        Set oControl = CallByName(oControl, "ActiveControl", VbGet)
        Loop Until TypeName(oControl) <> "Multipage"
    Set oControl2 = oControl.Pages(oControl.Value).ActiveControl   '''to set the control for the 1st child within the parent Multipage
    Do
        Loop Until TypeName(oControl2) <> "Multipage"
        
        Set RealActiveControl = oControl2
    Else
    
    Do
        Set oControl = CallByName(oControl, "ActiveControl", VbGet)
    Loop Until TypeName(oControl) <> "Frame"
    
    Set RealActiveControl = oControl
    
    End If

 

[davsy]

@Jaafar Tribak

I have found a way to update your Function to include Multipages. But I am still figuring a way to identify to total number of Multipages to create a loop.

This fuction only works with a parent Multipage.

Private Function RealActiveControl() As MSForms.Control
    Dim oControl As MSForms.Control, oControl2 As MSForms.Control
    On Error Resume Next
    Set oControl = oForm.ActiveControl
    Set oControl2 = oForm.ActiveControl
     
    If TypeName(oControl) = "MultiPage" Then    '''' This only works for parent Multipage
    Set oControl = oControl.Pages(oControl.Value).ActiveControl
    Do
        Set oControl = CallByName(oControl, "ActiveControl", VbGet)
    
    Do
        Loop Until TypeName(oControl2) <> "Multipage"
      
        Set RealActiveControl = oControl2
    Else
  
    Do
        Set oControl = CallByName(oControl, "ActiveControl", VbGet)
    Loop Until TypeName(oControl) <> "Frame"
  
    Set RealActiveControl = oControl
  
    End If

This function will allow us to detect the 1 child Multipage within the parent Multipage.
But while it detects the child, it does not detect the parent.

Still looking for a way to detect total number of Multipages so we can loop through all the Mulitpages and Controls to detect the child Multipage within the parent.
Do you think you can find a way for this?

Private Function RealActiveControl() As MSForms.Control
    Dim oControl As MSForms.Control, oControl2 As MSForms.Control
    On Error Resume Next
    Set oControl = oForm.ActiveControl     
    If TypeName(oControl) = "MultiPage" Then  '''to select Multipage  
    Set oControl = oControl.Pages(oControl.Value).ActiveControl
    Do
        Set oControl = CallByName(oControl, "ActiveControl", VbGet)
        Loop Until TypeName(oControl) <> "Multipage"
    Set oControl2 = oControl.Pages(oControl.Value).ActiveControl   '''to set the control for the 1st child within the parent Multipage
    Do
        Loop Until TypeName(oControl2) <> "Multipage"
      
        Set RealActiveControl = oControl2
    Else
  
    Do
        Set oControl = CallByName(oControl, "ActiveControl", VbGet)
    Loop Until TypeName(oControl) <> "Frame"
  
    Set RealActiveControl = oControl
  
    End If

 

[davsy]

Sorry there is some error. To set focus within parent multiple should be without oControl2.

I guess i can adjust this specifically for my project by determining multipage value and to apply function specially.

No universal solution.

Private Function RealActiveControl() As MSForms.Control
    Dim oControl As MSForms.Control, oControl2 As MSForms.Control
    On Error Resume Next
    Set oControl = oForm.ActiveControl
       
    If TypeName(oControl) = "MultiPage" Then    '''' This only works for parent Multipage
    Set oControl = oControl.Pages(oControl.Value).ActiveControl
    Do
        Set oControl = CallByName(oControl, "ActiveControl", VbGet)
        Loop Until TypeName(oControl) <> "Multipage"
    
        
        Set RealActiveControl = oControl
    Else
    
    Do
        Set oControl = CallByName(oControl, "ActiveControl", VbGet)
    Loop Until TypeName(oControl) <> "Frame"
    
    Set RealActiveControl = oControl
    
    End If

 

[Jaafar Tribak]

Sorry there is some error. To set focus within parent multiple should be without oControl2.
I guess i can adjust this specifically for my project by determining multipage value and to apply function specially.

Thanks @davsy
I will take a closer look at this later on when I am not too busy.
Regards.

 

[davsy]

@Jaafar Tribak

I think i'll consider this solved for my project.
Although not the most elegant code, but i got to get it to work.

What i did was keep adding IF statement to check if the nested multipage has been cleared.
Attached is the modified WB with multipage added.

UserFormFocus-MultiPage.xlsm

Private Function RealActiveControl() As MSForms.Control
    Dim oControl As MSForms.Control, oControl2 As MSForms.Control, _
    oControl3 As MSForms.Control, oControl4 As MSForms.Control
    On Error Resume Next
    Set oControl = oForm.ActiveControl
    Set oControl2 = oForm.ActiveControl
    Set oControl3 = oForm.ActiveControl
    Set oControl3 = oForm.ActiveControl
    Set oControl4 = oForm.ActiveControl 
     
    If TypeName(oControl) = "MultiPage" Then
    Set oControl = oControl.Pages(oControl.Value).ActiveControl
    Do
        Set oControl = CallByName(oControl, "ActiveControl", VbGet)
        Loop Until TypeName(oControl) <> "Frame"
       
        Set RealActiveControl = oControl
     
''''''' DETECT IF IT'S STILL A MULTIPAGE, AND 1 MORE LAYER'''''''''''''''
    If TypeName(oControl) = "MultiPage" Then
           
    Set oControl = oControl.Pages(oControl.Value).ActiveControl
    Do
        Set oControl = CallByName(oControl, "ActiveControl", VbGet)
        Loop Until TypeName(oControl) <> "Frame"
    Set oControl2 = oControl
    Do
        Set oControl2 = CallByName(oControl2, "ActiveControl", VbGet)
        Loop Until TypeName(oControl2) <> "Frame"
       
        Set RealActiveControl = oControl2
           
''''''' DETECT IF IT'S STILL A MULTIPAGE, ADD ANOTHER LAYER'''''''''''''''
    If TypeName(oControl) = "MultiPage" Then
           
    Set oControl = oControl.Pages(oControl.Value).ActiveControl
    Do
        Set oControl = CallByName(oControl, "ActiveControl", VbGet)
        Loop Until TypeName(oControl) <> "Frame"
    Set oControl2 = oControl
    Do
        Set oControl2 = CallByName(oControl2, "ActiveControl", VbGet)
        Loop Until TypeName(oControl2) <> "Frame"
       
    Set oControl3 = oControl2
    Do
        Set oControl3 = CallByName(oControl2, "ActiveControl", VbGet)
        Loop Until TypeName(oControl3) <> "Frame"
       
        Set RealActiveControl = oControl3
      
''''''' DETECT IF IT'S STILL A MULTIPAGE, ANOTHER LAYER MORE'''''''''''''''
    If TypeName(oControl) = "MultiPage" Then
           
    Set oControl = oControl.Pages(oControl.Value).ActiveControl
    Do
        Set oControl = CallByName(oControl, "ActiveControl", VbGet)
        Loop Until TypeName(oControl) <> "Frame"
    Set oControl2 = oControl
    Do
        Set oControl2 = CallByName(oControl2, "ActiveControl", VbGet)
        Loop Until TypeName(oControl2) <> "Frame"
       
    Set oControl3 = oControl2
    Do
        Set oControl3 = CallByName(oControl2, "ActiveControl", VbGet)
        Loop Until TypeName(oControl3) <> "Frame"
       
        Set RealActiveControl = oControl3

''''''' DETECT IF IT'S STILL A MULTIPAGE, ANOTHER LAYER MORE'''''''''''''''

    If TypeName(oControl) = "MultiPage" Then
           
    Set oControl = oControl.Pages(oControl.Value).ActiveControl
    Do
        Set oControl = CallByName(oControl, "ActiveControl", VbGet)
        Loop Until TypeName(oControl) <> "Frame"
    Set oControl2 = oControl
    Do
        Set oControl2 = CallByName(oControl2, "ActiveControl", VbGet)
        Loop Until TypeName(oControl2) <> "Frame"
       
    Set oControl3 = oControl2
    Do
        Set oControl3 = CallByName(oControl2, "ActiveControl", VbGet)
        Loop Until TypeName(oControl3) <> "Frame"
       
        Set RealActiveControl = oControl3


    Set oControl4 = oControl3
    Do
        Set oControl4 = CallByName(oControl3, "ActiveControl", VbGet)
        Loop Until TypeName(oControl4) <> "Frame"
       
        Set RealActiveControl = oControl4

''''''''''' WHEN ALL LAYERS OF MULTIPAGE IS CLEARED'''''''''''''''
''''''''' ADD ANOTHER LAYER IF NESTED MULTIPAGE IS NOT DETECTED''''


    Else
   
    Do
        Set oControl = CallByName(oControl, "ActiveControl", VbGet)
    Loop Until TypeName(oControl) <> "Frame"
   
    Set RealActiveControl = oControl
   
   
    End If
    End If
    End If
    End If
    End If
   
End Function