The following Microsoft Visual Basic for Applications (VBA) procedures allow you to create and manipulate a UserForm through VBA code. This article illustrates how to use code to add a text box, a check box, and a command button to a form.
Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs.
If you have limited programming experience, you may want to contact a Microsoft Certified Partner or Microsoft Advisory Services. For more information, visit these Microsoft Web sites:
Microsoft Certified Partners - https://partner.microsoft.com/global/30000104
Microsoft Advisory Services - http://support.microsoft.com/gp/advisoryservice
For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS
The following sample code creates a UserForm, changes its properties, and adds controls to it.
For these VBA objects to work, you must reference the Microsoft Visual Basic for Applications Extensibility library. To load the library into the current VBA project, follow these steps:
- Press ALT+F11 to start the Visual Basic Editor.
- On the Tools menu, click References.
- Click to select the Microsoft Visual Basic for Applications Extensibility check box under Available References, and then click OK.
The VBA object model in Microsoft Office 2000 provides the objects and methods for accessing the Visual Basic Editor projects and their elements. The top object for accessing VBA objects is the VBE
object, which can be accessed through the Application
object. The VBE
object, in turn, contains a collection of VBProject
object, in turn, contains the VBComponents
collection. Dialog boxes, also called UserForms, are represented as UserForm
objects and code modules are elements of the VBComponents
In Microsoft Word for Windows 2000, each document, as well as every template, can contain its own VBProject
object. In the Word documents, the VBProject
object is located underneath the Document
VBE Projects and Components
The following sample code displays a message box for each of the components in the active project. Each message box displays the name of the component.
Sub VbeCompName() X = VBE.ActiveVBProject.VBComponents.Count For I = 1 To X Msgbox VBE.ActiveVBProject.VBComponents(I).Name Next IEnd Sub
Use one of the following subprocedures to display a message box with the total number of components in the active project.
Sub VbeCompName() MsgBox Application.VBE.ActiveVBProject.VBComponents.CountEnd Sub
Sub VbeCompName() MsgBox ActiveDocument.VBProject.VBComponents.CountEnd Sub
(In Microsoft Excel, replace ActiveDocument
; in Microsoft PowerPoint, substitute ActivePresentation
Creating a New UserForm
The following sample code creates a new UserForm and assigns the variable MyNewForm to it. You can now use this new VBComponent
object to manipulate the User Form.
Sub BuildMyForm() Set MyNewForm = _ VBE.ActiveVBProject.VBComponents.Add(ComponentType:=vbext_ct_MSForm)End Sub
Changing the Name and Other Properties of a UserForm
The following sample code creates a new UserForm and then changes the name, caption (text shown in the title bar), height, and width of the newly created dialog box.
Sub BuildMyForm() Set mynewform = _ ActiveDocument.VBProject.VBComponents.Add(vbext_ct_MSForm) With mynewform .Properties("Height") = 246 .Properties("Width") = 616 .Name = "HelloWord" .Properties("Caption") = "This is a test" End WithEnd Sub
Adding Controls to the UserForm
The following sample code adds a check box control to a newly created UserForm and sets the name, caption, position, and size of the control. To add a control to a UserForm, you must first access the Designer Property
object of the corresponding VBComponent
object. The Designer
object (in this case, the ToolBox controls) is the object that permits manipulation and access to the controls' properties.
Sub BuildMyForm() Set mynewform = _ ActiveDocument.VBProject.VBComponents.Add(vbext_ct_MSForm) Set myCheckBox = mynewform.Designer.Controls.Add("Forms.CheckBox.1") With myCheckBox .Name = "Check1" .Caption = "Check here" .Left = 10 .Top = 10 .Height = 20 .Width = 60 End WithEnd Sub