Drawing UserForms in the VBA code editor
Part one of a seven-part series of blogs

Learn how to create your own custom dialog boxes in VBA, using UserForms. This is the first part of a three-part series (the other parts are on Writing Code for UserForms and Creating Advanced Controls).

  1. An Introduction to UserForms in VBA (this blog)
  2. Creating and Using UserForms in VBA
  3. The Properties Window
  4. Selecting Forms and Controls
  5. Formatting Forms and Controls
  6. Grouping, Aligning, Sizing, Spacing and Arranging
  7. Controlling How Form Keys Work

This blog is part of our Excel VBA tutorial series.  We also run training classes in Excel and courses in VBA.

Posted by Andy Brown on 28 February 2012 | no comments

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

An Introduction to UserForms in VBA

What UserForms are

A UserForm (or just form) is a dialog box that you create yourself.  You've almost certainly already seen some pre-created forms in Visual Basic for Applications:

However,neither of these can be customised much.  A UserForm, by contrast, can have lots of widgets and formatting on it:

Drinks order form

This user form allows someone in your office to order a drink.

 

UserForms aren't limited to Excel: Word, PowerPoint and SharePoint Designer use the same software (although Access and Outlook, typically, do their own thing).

Pros and Cons of UserForms

Should you learn to create and use UserForms?  Have a look at the pros and cons below, and make up your own mind!  Let's start with the good things:

Advantage Notes
Impressiveness There's nothing like a custom form laden with drop down lists, command buttons, pictures and formatting to impress a client or manager.
Ease of use For people who don't enjoy inputting data directly into Excel, you can create a front-end form instead and make it as easy to use as possible.

Those are powerful advantages; how about the other side of the coin?

Disadvantage Notes
Validating data Creating user forms is easy; making sure that users input data correctly takes longer.  On a worksheet you can use tools like data validation and protection to ensure users enter the right type of data, but in a form you're going have to work much harder to avoid errors.
Flakiness My experience from many years of training on Excel and writing systems is that user forms occasionally crash - Excel itself rarely does.

 

Now that you know what a form is, let's look at how to create one, and how to attach macros to it.