If you need to know which control on a userform has the focus you could consider to use the ActiveControl property of the UserForm. However, this property does not necessarily point to the control having the focus.
In the attached workbook you find UserForm1 with MultiPage1 with two pages, TabStrip1 with two tabs, and some textboxes and labels.
After startup textbox A1 on PageA has the focus. Now the property
UserForm1.ActiveControl
points to MultiPage1, and not to TextBoxA1.
If at any time you need to know which of the two textboxes on PageA has the focus you must use:
UserForm1.MultiPage1.PageA.ActiveControl
or
UserForm1.ActiveControl.SelectedItem.ActiveControl
Both properties point to TextBoxA1 after startup.
On PageB however, Frame1 is the container of two textboxes. Then you must use:
UserForm1.MultiPage1.PageB.Frame1.ActiveControl
or
UserForm1.ActiveControl.SelectedItem.ActiveControl.ActiveControl
This will point to TextBoxB1, TextBoxB2 or Nothing.
Path to active control
In general, to find the active control on any UserForm the property Userform.ActiveControl is your starting point, and from there you have to go down the hierarchy:
Private Function fnGetActiveObjectPath() As String
Dim obj As Object, sPath As String
sPath = Me.Caption
Set obj = Me.ActiveControl
Do
If obj Is Nothing Then
sPath = sPath & " - Nothing"
Exit Do
ElseIf TypeOf obj Is MSForms.Frame Or TypeOf obj Is MSForms.Page Then
sPath = sPath & " - " & obj.Name
Set obj = obj.ActiveControl
ElseIf TypeOf obj Is MSForms.MultiPage Or TypeOf obj Is MSForms.TabStrip Then
sPath = sPath & " - " & obj.Name
Set obj = obj.SelectedItem
Else ' must be Tab or native control
sPath = sPath & " - " & obj.Name
Exit Do
End If
Loop
fnGetActiveObjectPath = sPath
End Function
This function is implemented in the attached workbook: press F5 at any time to get the complete path to the active object. Examples of the resulting string:
UserForm1 - MultiPage1 - PageA - TextBoxA1 UserForm1 - MultiPage1 - PageB - Nothing UserForm1 - MultiPage1 - PageB - Frame1 - TextBoxB2 UserForm1 - TabStrip1 - Tab2 UserForm1 - TextBoxT UserForm1 - Nothing
Explanation:
- After startup press F5.
- After startup click on the tab of PageA, then click on the tab of PageB (which now has the focus) and press F5.
- After 2. click on TextBoxB2 and press F5.
- After startup click twice (!) on Tab2 and press F5.
- After startup click on textbox T and press F5. Note that this textbox is contained in the userform and not in the tabstrip.
- Disable all controls on the userform, restart and press F5.
Final notes
- The container objects UserForm, Frame and Page all have an ActiveControl property. MultiPage and TabStrip however, are not containers but basically collections, of which one item is selected: SelectedItem. This explains why we have to treat these objects differently in the above code.
- After startup click on tab PageA. Notice that the textbox lost the focus, the tab got the focus. However, after pressing F5 we see that TextBoxA1 still is the active object. Conclusion: the active object is not necessarily the control having the focus.
- Path example 2 above shows that initially the ActiveControl property of a Page is Nothing. This property is set after the first visit of the page, and will remain so. For example: after startup click on tab PageB. Now the property UserForm1.MultiPage1.PageA.ActiveControl is still valid, and points to TextBoxA1.
After startup, if the user clicks tab PageA the focus moves to the tab. Unfortunately, at this point no Exit or Enter event is fired. This makes it virtually impossible to determine if a native control has the focus.
I hope this pixcel helps. Excel!
Found this code and it fits into this article like a hand in a glove. I have found this to be extremely useful.
Function RootActiveControl(Optional Container As Object) As Object
‘Code attributable to Mike Erickson, MrExcel MVP
Dim tActiveControl As Object
If Container Is Nothing Then Set Container = Me
Set tActiveControl = Container.ActiveControl
Select Case TypeName(tActiveControl)
Case “Frame”
Set RootActiveControl = RootActiveControl(tActiveControl)
Case “MultiPage”
With tActiveControl
Set RootActiveControl = RootActiveControl(.Pages(.Value))
End With
Case Else
Set RootActiveControl = tActiveControl
End Select
End Function
Hi Frans,
Just a thought,
“After startup, if the user clicks tab PageA the focus moves to the tab. Unfortunately, at this point no Exit or Enter event is fired. This makes it virtually impossible to determine if a native control has the focus”
For a TextBox or ComboBox, you can determine if ít’s got the focus by using the CurX property,
example:
myTest = UserForm1.MultiPage1.PageA.ActiveControl.CurX will raise an error if the active control (Text- or ComboBox) doesn’t have the focus
Excel!
Eric
Hi Eric,
Thanks for your comment and sharing your great idea.
This works fine for text and comboboxes, as you say. Unfortunately other controls don’t have a CurX property.
Regards,
– Frans
Hi Frans,
I was just trying to figure out whether it was possible, I found in the hidden properties of Control “.InSelection”
This property gives true when a control is selected and false when it isn’t. (as far as I can see now, have to test some more)
so clicking on the Multipage itself gives true, on the Tab of the Multipage gives false:
MsgBox UserForm1.MultiPage1.PageA.ActiveControl.InSelection
So there’s still something to play with 😉
Best,
Eric