Skip to: Site menu | Main content

Login

Name: 
Password:
Remember me?
Register

Userforms

written by Helen Toomik - Last updated Oct 2004

Contents:

Caveats... Please note that this is not "Excel VBA 101" - I assume you are already familiar with VBA and in particular Excel. I developed this primarily for Excel 2000 and 2002/XP, and some parts are not applicable to Excel 97.

Thanks... to everybody who helped with proofreading, debugging and other suggestions.

Introduction - What is a userform?

A userform is a custom dialogue box that you can use when MsgBox and InputBox are just not enough. Userforms can be used for displaying as well as inputting data. You could even build an entire user interface using userforms, and never let the user touch the spreadsheet itself.

Most of the things that you can see in Windows dialogue boxes can be replicated in userforms - commandbuttons, option buttons, checkboxes, textboxes, comboboxes - plus a whole lot of other, more rarely used controls like graphs, calendars, etc.

This tutorial is an introduction to basic userform concepts, as well as commonly used userform techniques. I'll explain some of the more general ideas "verbally", but I'm also including a sample file where you can see how it all works in practice, so the tutorial text itself won't have many code samples.

I've put this together mainly for Excel, but the principles (and most of the code) would also apply to other MS Office applications. The main difference would be in the parts that transfer data between userform and your document.

Getting started

  1. Create a form. Open the Visual Basic Editor (Tools...Macros... or Alt+F11). Add a userform by choosing Insert...Userform from the menu bar, or right-click in the project explorer and do the same thing from there.
  2. Add controls. When you create a userform, a new floating toolbox window will appear, called the Controls Toolbox. (If it doesn't, you can find it in the View menu.) This shows the most common types of controls. Click on one that you like, and simply draw the control in your userform.
  3. More controls. Many more controls are available but not immediately visible. Right-click on the Controls Toolbox and choose Additional Controls. You can add calendars, spreadsheets, graphs, etc.
  4. Add event procedures. Events allow the form and its controls to respond to what the user does. Event procedures sit in the code module associated with the userform (its class module). You can access the code by double-clicking on the form/control, or right-clicking on the form/control and choosing "View Code", or right-clicking on its icon in the project explorer. See the events below.
  5. Show the form. To show the userform, open the form's class module and hit F5, like you'd do for running a macro, or click the Play button on the toolbar. Note that pressing F5 in a class module does not run the sub that your cursor is in, but the userform itself.
  6. Debug. As with macros, F8 allows you to step through the code line by line.

Forms toolbar controls vs. Control toolbox controls

If you have used controls in Excel, you may have used controls from the Forms toolbar, rather than the Controls toolbox. The two sometimes look very similar, but work quite differently behind the scenes. Ozgrid has a fairly good overview of the differences.

Finding out more

If you want to know more after reading this tutorial, there are three easy ways:

  1. The example file I've included. It has examples for the commands I've mentioned here, in particular all the FAQs, and some more.
  2. Help. The VBA help files for userform-related commands are fairly good. Take a look at their examples, too.
  3. Object browser (F2). Shows you what properties and methods are available for a certain object. Pressing F1 here shows the Help section for that object/method/property.

Properties

Forms and controls have properties (like size, location, etc). You can change these while setting up the form, and most of them can also be changed at runtime (via code). The values you choose while setting up the form will be default values, and runtime changes will only be valid while the form is loaded (see Life cycle of a form below).

The two most important properties of objects are .Name and .Value:

For example: 

txtFirstName.Text = Range("A1").Value 
Range("B1").Value = optVegetarian.Value

A good habit

Give your forms and controls descriptive and systematic names. You'll often find it helpful to give each type of control a prefix: frm for forms (frmMain and frmDetails), opt for option buttons etc. This makes your code easier to read, and also allows you to use some techniques that would otherwise be more complicated (see Looping through controls below).

http://www.xoc.net/standards/rvbanc.asp has a lot more detail on naming variables and objects.

Referring to controls and forms, and Me

When referring to the controls of a userform from code in its class module, you can refer to them by name: txtFirstName.Value = "John". For controls in other userforms, the name of the control is preceded by the name of the form (e.g. frmEmployees.lstDepartments).

The same approach is valid for public variables declared in the form's class module - these behave essentially as properties of the form (e.g. frmInputs.AllDone). Note that you cannot declare a variable as Global in the class module of a userform.

Me is a shortcut for referring to the userform from its class module (e.g. Me.Height). For example, referring to TextBox1 in UserForm1: 

 
Me.TextBox1.Text ="Example" 
'in the userform's class module, or: 
UserForm1.TextBox1.Text ="Example" 
'in a different module

The life cycle of a userform - showing and closing, etc.

To show your form manually, press F5 in the form window or in its class module. If there is a Userform_Initialize procedure in the module (see Events), that will run first.

To show and hide your form via code (for example, you might want a commandbutton on frmIntro to hide frmIntro and show frmMain instead), use the .Show and .Hide methods.

Advanced topic: Load and unload

Behind the scenes, there's more than just showing and hiding going on. Before a form is shown, it needs to be loaded into memory. If you show a form that hasn't been loaded, it will load automatically.

Indeed, any reference to the form, or to a variable or control or property of the form, will force it to load, and therefore trigger the Initialize event.

If you want to initialise the form without showing it, you can load it like this: 

Load frmMain

After hiding a form, it will still be loaded. If you show it again, the Initialize procedure will not run again. (The Activate procedure will, however.) To clear the form from memory, you have to unload it. When the user closes a form with the close button, the form is automatically unloaded.

So the sequence is: Load - Show - ... - Hide - Unload.

Unload clears all variables in the form's module - it is comparable to stopping a procedure. Any values that the user has entered will be lost, and controls will revert to their default values that you have entered using the Properties window. If you want to save their values, you need to do so before unloading the form.

Modal vs modeless

Forms can be shown in one of two "modes" - modal or modeless. Modal forms do not allow the user to do anything else in Excel while the form is visible - like a MsgBox. Modeless forms allow the user to move around in Excel, do other things, and then return to the form.

Forms can be shown in one of two "modes" - modal or modeless. Modal forms do not allow the user to do anything else in Excel while the form is visible - like a MsgBox. Modeless forms allow the user to move around in Excel, do other things, and then return to the form.

Code execution will also continue in the background while a modeless form is shown. You can make your code wait until the form has been closed using a loop that checks whether the form has been closed: 

 
Do Until frmOther.Visible = FALSE 
    DoEvents 
Loop

The default setting is modal (NB: the opposite of the case in VB6). Once a form is shown as modal, you cannot change it to modeless - you have to hide the form and then show it again, specifying that you want it modeless.

Modeless forms are only available from Excel2000 onwards.

Events

This is where things get interactive. Events allow the form and its controls to respond to what the user does. You are probably familiar with events from Excel VBA - Workbook_Open, Worksheet_Change etc. While you can do a lot in Excel without events, forms are pretty useless without them.

To insert an event procedure, right-click on the object and choose "View code". An event procedure is created automatically for that control's standard event. To create a procedure for a different event, choose the event you want from the drop-down menu at the top right of the VBE window. Alternatively, go to the form's class module and choose the object from the left drop-down and the event from the right drop-down.

Userform_Initialize

The most important event for forms is the Initialize event. Initialize is pretty much the first thing that happens to a form - the Initialize event is triggered as soon as the form starts loading, either because it is called by code or by the user (by hitting F5 or F8).

This is where you would initialise variables and controls. For example, you can update textboxes with latest values from the spreadsheet, change the default value of a textbox to today's date, etc.

QueryClose and Terminate

The equivalent "end of life" events for a form are two: QueryClose and Terminate. QueryClose occurs first, and gives you the chance to cancel it (and not close the form); Terminate is final and not possible to cancel.

So the sequence of events is: Initialize - ...- QueryClose - Terminate.

Userform_Activate

If you hide a form without unloading it, and then show it again, Initialize won't run again. Instead, the Activate event occurs. Activate is triggered every time the form gets focus. This happens each time the form is shown. If you have several forms visible at the same time, the Activate event is also triggered every time you switch between forms.

Events with parameters

As with Excel events, some events have parameters, which give you more information about how and why the event was triggered - the UserForm_KeyDown event tells you which key was pressed, etc. When you create an event procedure for one of those events using the drop-down menus in VBE, the procedure is automatically set up to properly capture all the parameters.

Some parameters are read-only, whereas others are read/write. For example, the Userform_QueryClose event, which occurs before a form is closed, has a Cancel parameter. Setting Cancel = True within the procedure cancels the event, and the form is not closed.

FAQ: How can I pass data between userforms?

There are two main approaches to passing data between forms. Either you pass data directly from form to form (which means that the two forms are loaded in memory simultaneously) or you store the data somewhere and then access it later from the other form.

Passing from form to form can be done from either the "source" form or the "target" form. Remember that this has to be done before the source form is unloaded. Also remember that when referring to controls in another form, you need to specify the form name: 

txtName.Value = frmTheOtherForm.txtName.Value

Passing data directly from form to form will trigger the Initialize event of the other form (unless that form is already visible) since you are referencing its controls' properties. If the Initialize event procedure in its turn includes code that shows the form, or calls other subs, this can easily trigger a lot of code, and get difficult to debug, so I'd use this approach with relatively simple forms only.

In comparison, storing the data in a variable allows more flexibility and control. The data could be stored either in a public variable, in a worksheet cell (so that it can be saved when the file is closed) or in a name in the worksheet.

The sample file has examples of both approaches.

FAQ: How can I do ... with all the ... in my form?

For example, how can I add up the values of all textboxes? uncheck all checkboxes? etc.

You can loop through all the controls in your form, pick out those that are of the right type, and apply the relevant code to those.

As with most things, there are several ways of doing this... using TypeName, TypeOf or control names.

TypeName()

TypeName returns a string - "TextBox" for textboxes, "CommandButton" for commandbuttons etc. The typename of a control is generally the same as the control's default name & caption, but without the number. For example, when you add a new textbox to your form, it is called TextBox1 by default.

TypeName is case-sensitive, and if you make a typo, you get no error message - the code simply won't work properly.

TypeOf

TypeOf is a fancier way of doing the same thing. Instead of a string, it returns the object type directly, referencing the object library. You can find the library and type of an object through the Object Browser. For example, the type of a textbox is MSForms.Textbox.

The main practical advantage of TypeOf is that it makes your code easier to debug. Typos get caught when you try to compile the module, and TypeOf supports Intellisense - start typing If TypeOf ctl Is, and you'll get a dropdown list with all the available choices.

"If TypeOf ... Is ... Then" is considered a special case of If statements, and is covered in VBA help under If.

Control name

If you have named your controls consistently, you can use the Name property to identify them, with the help of the Left() function. This "low-tech" approach is somewhat more flexible - you could use this to identify a subset of all textboxes. If, for example, your form has 10 textboxes (txtTeamName, txtTeamNumber, txtMember1, txtMember2, ..., txtMember10) you could use this approach to identify and empty all textboxes whose name starts with txtMember.

Examples

The three alternative If statements in this example would all achieve more or less the same thing (assuming the names of your textboxes all start with "txt"): 

 
Dim ctl As Control 
For Each ctl In Me.Controls 
    If TypeOf ctl Is MSForms.Textbox Then 
        'or 
        'If TypeName(ctl) ="TextBox" Then 
            'or 
            'If Left(ctl.Name, 3) ="txt" Then 
                'do something With the textbox 
                ctl.Text ="Hello" 
            End If 
        Next ctl
There's an example in the sample file above, too.

FAQ: How can I get data into my listbox?

RowSource

You can link a listbox directly to a range in your worksheet. This is similar to having a listbox in the worksheet. Any changes you make to that range will immediately be reflected in the listbox. The link is read-only: you cannot change items in the listbox to change the worksheet.

Note that the parameter for RowSource is a string and not a range: 

lstNames.RowSource = Range("Names").Address

List / Column

You can set list items using the List and Column properties. The two are essentially the same, except for their orientation: the syntax is List(row, column) and Column(column, row). Both can be used to copy an entire array to the listbox, or to set individual items. 

lstNames.List = Range("Names").Value

AddItem

AddItem adds a single row to the listbox, and can put a value in the first column of the new row. For multicolumn listboxes, you'll have to use List or Column to put in values in the rest of the columns. 

With lstNames 
    .Additem 
    .List(0, 0) ="John" 
    .List(0, 1) ="Smith" 
End With

You'll find examples of all these three methods in the sample file.

FAQ: How can I create a progress bar for my loop?

One easy way to create a progress bar is to create a form with two labels (call them lblBackground and lblProgressBar for example). Make lblBackground as wide as you want the progress bar to be, and set the width of lblProgressBar equal to 0 to start with. Also make sure that lblProgressBar is in front of lblBackground. Then increase the width of lblBar as you run the loop. 

Me.lblProgressBar.Width = p * Me.lblBackground.Width 
'where p Is the proportion of the Loop that's done, For example 0.75

Again, there's an example in the sample file.