User form controls

We’ve shown you how to use Excel’s standard message and input dialog boxes, but sometimes you’d like to design an input box with a lot more flexibility. Excel VBA allows you to do just that. Designing your own custom user forms is a big subject. We’d like to do our best to help you get started, but you might want to reserve a bit of time to tackle user forms for the first time. This lesson is a bit longer than some of the others.

Custom user forms are great with complicated inputs

An example of a custom user form in Excel VBA

When we were looking at message boxes we had some example macro code. We’re going to develop that code further here. At the moment, the macro calculates the returns generated by an investment as an IRR or a money multiple (go back to the message boxes section if you would like to view the macro code and run it or download the example).

Imagine that things have gotten more complicated. Instead of giving the user the choice of expressing returns as an IRR or a money multiple (2 outputs), we would also like to give them the choice of calculating NPV (3 outputs). Things have become more complicated. Now might be a good time to think about using a VBA custom user form.

The first step: insert userform

Getting started with VBA custom user forms: step 1

Take a copy of the code used in the message boxes section, or download the example here: complex message box. We’re going to use that code as our starting point.

Once you’ve opened the VBA editor (“Alt” “F11”) and have the code displayed, click on “Insert” “Userform”. A blank template opens up like the one below.

VBA user forms: what next?

There’s a lot to user forms! To get one working we’re going to need to:

  • Learn how to view user form macro code – creating a user form creates a bit of extra macro code relating to each component of the user form
  • Structure the user form macro code – get each piece of your user form code doing exactly what you want
  • Get the user form working by getting the right code in the right place, calling the user form and being careful how we declare our macro variables.

Don’t worry though. We’ll talk you through all the steps in detail. If you can follow this tutorial though, once you’ve started playing and once you’ve created your first user form it should all start to make a bit more sense.

We’ve already just started by creating a blank user form. Next we’re going to add some simple options to our blank form (giving the user the ability to select between three different macro outputs). So the very next thing we need to discover is how to add option buttons to our user form.

About this online Excel course material

You’re looking at a free online course extract covering different kinds of Excel VBA macro dialog boxes.

...join the conversation...

Your email address will not be published. Required fields are marked *