Many great features in Excel let you do a range of things in straightforward ways. However, as you progress and want to be able to do more complex ideas and create processes, then Userforms are the way to go.
What are Excel Userforms?
A Userform is an object that represents a window or a dialogue box that works within the user interface of Excel. When you work with Userforms, you can quickly create custom dialogue boxes, each held within a Userform object.
Another way to look at it is that Userforms are a blank canvas. You can place elements that are called controls onto them, which are also objects. Userform controls are the objects that users work with to provide input through the Userform. The most common use for them is within Excel built-in dialogue boxes – once created, they act the same as these built-in features but work in the way that you set.
Benefits of Excel Userforms
The most significant advantage of Userforms is that they are very flexible. You can create extremely sophisticated and complex Userforms to perform any number of tasks and help you when using Excel.
Custom Userforms are easy to use for people who don’t enjoy inputting data directly into the spreadsheet. You can instead create front-end forms and ensure that the process is as simple as possible. Plus, if you are making software to work with a client, Userforms look very professional and impressive; making reporting more efficient.
Userforms also allow you to interact with your users in ways that the standard options of Message Boxes, Input Boxes or controls don’t let you.
Five steps to master Excel Userforms
The process of creating and using Excel Userforms is a relatively simple one.
1. Insert a new Userform
To begin with press Alt+F11 on the keyboard to go to the Visual Basic Editor. Go to Project Explorer and right click on the workbook where you want the Userform to function. On the context menu, go to Insert>Userform. This will insert an empty dialog box in the workbook along with a Forms node – if you already have a Forms node open, another won’t be added.
You will also see a floating window called the Toolbox. This contains the controls you can add to the Userform. Each Userform object contains a single dialog box so you need to open one for each box you want to make.
2. Add controls to the Userform
It takes just three steps to add controls to a Userform. First, select the control in the toolbox – this might be a label, for example. Click on the Userform in the spot where you want the control to be located, and it will appear at the default size. Move or resize the control with standard actions to make it the size and dimensions you require.
3. Master the toolbox
To master Excel Userforms, you need to learn the toolbox and the controls that it contains. There are 16 icons in the toolbox, 15 of which can be added to Userforms. These break down into two groups with examples such as:
Controls that change the appearance of the Userform
- Labels – display text
- Frame – aesthetical or logical uses to group controls
- Multipage – container with tabs
- Tab Strip – several tabs in the box
- Image – adds an image
Controls that allow data entry, make selections or issue commands
- Textbox – an input field for text or values
- Combo Box – drop down list with one item visible at a time
- List box – shows a list with one or more items visible at a time
- Checkbox – select true or false
- Option Button – choose one or another
4. Learn to customise your Userform
There are a variety of settings to tailor the form and ensure it works as you want it to. The Properties window is an example where you can personalise the look of the form as well as set up how the controls work. This includes single, multiple and dominant controls.
You can also edit things such as the form caption, background and foreground colours and fonts or typefaces used.
5. Assign a VBA Code to the Userform
Once the form has the controls in place, the design and functions are correct, and you are happy with it, you can create and assign macros to the Userform. To do this, get the Code window for the form using the F7 shortcut. Then enter the appropriate VBA code into this Code window. The Userform is then ready to go.
