Userforms and userform controls are not part of the Excel Object Model but have their own package. In this article some general notes on the MSForms library.
The MSForms library
The Userform object is part of the Microsoft Forms 2.0 Object Libraray (FM20.DLL), better known as MSForms. The package contains:
- the UserForm, the root object which acts as a container for other objects;
- container objects Frame and Page;
- native controls, like Checkbox, ComboBox, Commandbutton and TextBox;
- other objects like the DataObject, Font object and HTML objects.
The UserForm is basically a container for ActiveX controls, and accepts ActiveX controls from other packages like RefEdit.dll and MSComCtl.ocx. Like the Worksheet object the UserForm object has it’s own code module.
MSForms has three collections:
- Controls: all Control objects in containers UserForm, Frame and Page;
- Pages: all Page objects in a MultiPage;
- Tabs: all Tab objects in a TabStrip.
The collection VBA.Global.UserForms holds all loaded UserForms in a workbook. Although the prefix vba.globals suggests that the collection is bound to the application instance this seems not the case.
ActiveX control containers
In this context the concept “container” has two meanings. Firstly, above we defined UserForm, Frame and Page as container objects for other objects. Secondly, the controls in MSForms are ActiveX controls and are contained in an ActiveX control container. Note the significant difference between the two concepts.
In a Userform object Me.TextBoxA points at native textbox control TextBoxA, with properties like BackColor and Locked. On the other hand, Me.Controls(“TextBoxA”) points at the ActiveX control container of TextBoxA, which does not have properties BackColor and Locked, but does have properties like Height and TabStop.
Sub test() Dim ctl As MSForms.Control Dim tbx As MSForms.TextBox Load UserForm1 With UserForm1 Set ctl = .Controls("TextBoxA") Set tbx = .TextBoxA Debug.Print ctl.Name, tbx.Name Debug.Print ctl.Parent.Name, tbx.Parent.Name Debug.Print ctl.BackColor, tbx.BackColor Debug.Print ctl.TabStop, tbx.TabStop End With End Sub
Check the Object Browser to learn more on the control object and native objects, or use the code above and Intellisense. For example: in the above code type “ctl.” anywhere and note that the Backcolor property is not exposed to us by Intellisense, since it is not a property of the MSForms.Control class. Nevertheless ctl.Backcolor is a valid statement, as shown above. For this reason you don’t have to care about the distinction between the native and container part of a textbox control most of the time. However, sometimes it helps to have a basic understanding. For example when you try to trap the events of a userform or textbox.
Container object UserForm
Obviously the UserForm object itself is not a member of the controls collection. An object of class MSForms.UserForm is contained in the UserForm window of window class ThunderDframe. This window handles the Userform Initialize, Activate, Deactivate, Resize, QueryClose and Terminate events. These events are exposed to VBA but are not part of the MSForms object library. In the VBA help you find them in the section Visual Basic Language Reference, and not in the Microsoft Forms Visual Basic Reference section. Strikingly, you won’t find them in the Object browser at all.
Compared to the documentation of the Excel Object Model the help on MSForms is rather poor. The VBA Language Reference has a special Microsoft Forms Visual Basic Reference section, but a more informative description on the UserForm object you find in the section Visual Basic Language Reference. To get a list of all members of an MSForms control you have to use Intellisense or find your way in the Object Browser (VBE F2).
Active-control versus control-having-the-focus
A userform has an ActiveControl property. If the userform holds container objects then this property does not necessarily point to the active control. Also, the active control is not necessarily the same as the control having the focus. See for more details.
I hope this pixcel helps. Excel!