Did you know that you can add and remove controls from a UserForm (also known as custom dialog
boxes) at run-time? This means that you can change your UserForms based on certain
conditions that exist during the operation of your VBA project—you’re not limited
to the design of the UserForm that you created in the VBE designer!
To illustrate the technique, we’ll create
a Word VBA Template which will prompt the user for the number of cars they
own. (It allows for up to 20—hopefully that will be enough for most of us!)
It will then display the correct number of textboxes to allow the user to
enter the make of each car. When the user is done, the list of cars will be
added to the document.
The key thing here we want to illustrate is
the part where the dialog box will actually change to display the correct
number of textboxes (with accompanying labels).
The primary method that accomplishes this task is Form.Controls.Add.
(You can look it up in VBA Help.) Its syntax is like this:
Set ctl = Me.Controls.Add(ControlClass,
For our purposes, ControlClass can have one of the following
These are text strings, so you must enclose them in quotes.
You can optionally specify the name of the new control using the Name
parameter, for example, "TextBox7”, "txtFirstName”, "lblPrompt”,
or "chkChicago”, whatever’s appropriate for your use.
Finally, you can choose to make the new control invisible by specifying False
for the Visible parameter. The default
is for Visible to be True.
So, for example, let’s say you would like
to add a new label to your form. The form is named frmMyForm, and the new
label should have the name "lblPrompt”. The method call would look like:
Dim myLabel as Label
Set myLabel = frmMyForm.Controls.Add
Don’t get confused about myLabel and lblPrompt.
myLabel is an Object variable in VBA which is now
set to refer to the control on the form by the name of "lblPrompt”.
Once you add a control to a form, you will certainly need to set some properties
on the control, at least things like Top, Left, Width
and Height. You can do this right after creation if you want, using,
in our example, myLabel:
.Left = 10
.Top = 10
.Width = 30
.Caption = "Enter your name:"
We’ve let the Height property default to whatever VBA sets for it initially.
Now later on in your code, if you want to
refer to the newly added control, but you no longer have the myLabel reference,
you can either use:
You cannot use the other syntax to
refer to a control:
when the control has been created dynamically using Controls.Add.
The example which accompanies this article, Dynamic UserForms Demo.dot,
is a Word template which illustrates the techniques we’ve just describes in
greater detail and in actual usage. You can download a copy of this template
demo by clicking: HERE.
When you open this template, the Document_New method will
call ShowCarsDialog, which will call the frmCars.Show
method. frmCars has a label (lblNumCars) and textbox
(txtNumCars), which prompt you to enter the number of cars you have. When you
click on the Show Cars button (cmdShowCars), you will see a
number of labels and textboxes equal to the number of cars you entered, allowing
you to enter a make for each car. Then, when you click the Done
button (cmdDone), all the car makes you entered will be inserted into the document
and the dialog form (frmCars) will be unloaded.
If you want to change the number of cars again
before you press Done, you can do that and when you
click on Show Cars, the number of car labels and textboxes will change again
to accommodate. So you can Add and Remove cars from
the list before you click Done.
When you look at the code, you will see that each car make has a label and
a textbox called lblCarN and txtCarN, where
N is the number of the car. Each label is
added to the form using this line of code:
Set theLabel = Me.Controls.Add
("Forms.Label.1", "lblCar" &
Each textbox is added to the form using this
line of code:
Set theTextBox = Me.Controls.Add
After creating each control, we set a number
of properties on it. We set each label’s caption using this line of code:
theLabel.Caption = "Car #"
& CurrentNumberOfCars & ":"
You can see we even set the Accelerator property on each label
to the number of the car using this line of code:
theLabel.Accelerator = CurrentNumberOfCars
If you tell the dialog box to remove some
cars, the following lines of code are used:
In this article, we’ve learned how to use Form.Controls.Add
to add controls to a form’s Controls collection at run-time. We then set properties
on the control to make it look and function the way we want. We also learned
how to dynamically remove a control from a form’s Controls collection using
Now you can experiment with adding and removing controls to your forms whenever
you need them. You will find many uses for this technique once you’ve learned
how to do it.
Need further help getting your VBA code working right? Check out our free
VBA support groups. See these links for details: http://groups.yahoo.com/group/Word_VBA/ and/or http://groups.yahoo.com/group/ExcelVBA/.