Dynamic UserForms

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, Name, Visible)

For our purposes, ControlClass can have one of the following values:

"Forms.CheckBox.1"
"Forms.ComboBox.1"
"Forms.CommandButton.1"
"Forms.Frame.1"
"Forms.Image.1"
"Forms.Label.1"
"Forms.ListBox.1"
"Forms.MultiPage.1"
"Forms.OptionButton.1"
"Forms.ScrollBar.1"
"Forms.SpinButton.1"
"Forms.TabStrip.1"
"Forms.TextBox.1"
"Forms.ToggleButton.1"

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 _
    ("Forms.Label.1", "lblPrompt")

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:

With myLabel

.Left = 10
.Top = 10
.Width = 30
.Caption = "Enter your name:"

End With

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:

frmMyForm.Controls("lblPrompt”)

or

frmMyForm!lblPrompt

frmMyform.lblPrompt

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" & CurrentNumberOfCars)

Each textbox is added to the form using this line of code:

Set theTextBox = Me.Controls.Add _
    ("Forms.TextBox.1", "txtCar" & CurrentNumberOfCars)

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:

Me.Controls.Remove "lblCar" & CurrentNumberOfCars
Me.Controls.Remove "txtCar" & CurrentNumberOfCars

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 Form.Controls.Remove.

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/.