User forms are an integral part of VBA programs, and it’s important to design them correctly to ensure correct behavior. User forms allow you to add an intuitive interface to your spreadsheets, for user interaction.

You can design elaborate forms to request input from a user and present the output in an Excel file. Forms give you a lot of flexibility to decide how to design your spreadsheet-based application.

Here are some of the details you’ll need to know when setting up your user forms.

Adding a VBA User Form in Excel

As a first step, you need to enable the Developer tab in Excel to provide quick access to the VBA developer tools:

  1. Open a new Excel workbook and click on the File tab at the top left.
  2. Select Options from the list of options.
  3. In the resulting box, navigate to the Customize Ribbon option.
  4. Check the Developer option under the list of Main Tabs (right-hand side).
  5. Click on OK, once done.
VBA Excel interface
  1. With the Developer tab available, you can click the Visual Basic icon to open the editor window. You can also open the code editor with Alt + F11.
  2. Click on the Insert menu and select UserForm. This will open a blank user form, which you can design as per your will.
Excel VBA code editor

The form also shows up in the left-hand tree view, under the Forms header. You can create many types of user forms, including data entry forms, customized inventory forms, and many more.

Using the Toolbox to Design the User Form

The toolbox is a vital tool you’ll use to design a user form. It contains all the tools you’ll need to create a useful application.

Select Objects

The Select Objects tool provides a cursor that lets you select, resize, and move controls around the form. Unlike the other toolbox items, it does not create a new control.

Label

A label control allows you to display text or numeric values on your form. Drag the label onto the form and set its height and width as per your requirements.

TextBox

When you want a user to input a value, add a TextBox to your form. A TextBox supports data entry and allows the user to change that data at a later time.

ComboBox

A ComboBox is a flexible control that supports both pre-defined input and freeform data entry. The user can select one item from a dropdown containing set values. They can also enter a different value in the text box.

Here’s some example code to demonstrate adding items to a ComboBox:

        ComboBox1.AddItem "Option1"

ComboBox1.AddItem "Option2"

ListBox

The ListBox control is like the pre-defined part of a ComboBox control. It forces a user to choose an option from those given in the list. There is no option to add free text in a ListBox.

CheckBox

A CheckBox control has one of two values: checked or not checked. The user can toggle between these states by clicking the CheckBox. Behind the scenes, your code can read the value of a CheckBox as a Boolean: true or false.

You can assign a value to a CheckBox to check or uncheck it:

        Checkbox1.Value = True
    

OptionButton

An OptionButton, sometimes called a radio button, belongs to a group of related options. Use OptionButtons in cases where the user should pick one value from a small set of values. Some common examples include:

  • Size: Small, Medium, Large
  • Contact preference: Email, Phone, Mail
  • Color: Red, Green, Blue, Yellow

Frames

A Frame allows you to group other controls. Frames can improve the visual layout and information hierarchy of your form, especially if it's a large one. They also make it easier to move or reorganize related controls.

To add a frame(s) to your form, drag it from the toolbox and resize it according to your needs.

CommandButton

A CommandButton is one of the most important controls in a user form. Your users can click a button that will then perform an action you have specified. You can add several buttons to your form, and cater for common actions like saving data, closing the form, or submitting form data.

To edit the action associated with a button, drag it to the form, and double click to open the code editor. For example, you might clear a form via a Reset button like so:

        With Me

.TextBox1 = ""

.TextBox2 = ""

.OptionButton1.Value = False

End with

Multipage

A Multipage control is suitable when you want to add several tabs to your form. You can design each tab independently to group functionality. This control is valuable for good information architecture to separate different functions and reduce the visual complexity of your app.

Image

This control displays an image on your form for aesthetic purposes.

Scrollbar

A Scrollbar is a multi-function control that can support navigation or act as an input device. The default Scrollbar is vertical, but you can choose to create a horizontal version instead. A Scrollbar is useful when presenting many options for a user to select from. You can write code to attach a Scrollbar to the values in a TextBox or other control.

SpinButton

You can use a SpinButton with another control to increment and decrement numerical values. You can also use it to scroll through a range of values or a list of items.

Working With User Forms in VBA

User forms in VBA are powerful and offer many controls that you can use to develop an application. Consider what kind of data inputs your application needs to work with when choosing which controls to use. With a well-designed form, you can build a basic application that runs on top of Excel or other Microsoft Office apps.