Writing VBA macros to work with user forms
Part two of a three-part series of blogs

The previous part of this three-part series showed how to draw user forms; this part shows how to write macros to get them to work (the final part shows how to use some of the more advanced controls, such as combo boxes and MultiPages).

  1. Coding Forms - the Basics
  2. Form and Control Events (this blog)
  3. Validating Form Data

This blog is part of our Excel VBA tutorial blog.  If you want classroom training (for small groups of up to 6 people), take a look at our courses in VBA.

Posted by Andy Brown on 28 February 2012

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.

Form and Control Events

The key to understanding a user form is to understand that there are lots of events happening in its life (similar to worksheet and workbook events).

The Importance of Naming Controls

The first thing you need to do to code a form is to make sure the bits you want to code have got sensible names:

Form with names attached

We'll call the controls:


This naming convention - using the first 3 letters of a name to denote what sort of a thing it is - is called Modified Hungarian notation.  Using this convention, I would be owlAndy.

Renaming a command button

Renaming the Order command button is shown here (you just change the Name property, which appears at the top of the list of properties).


Attaching Code to Events by Double-Clicking

The easiest way to attach code to an event for a control is to double-click on it:

View Code menu for a button

You can right-click on any object and choose to view its code, or (more simply) just double-click on it.


The UserForms application will automatically assign code to the Click event for this object (ie control what happens when you click on it):

A cancel button's code

By default Excel assumes you want to assign code to the click event of a button.


Typically you would close a form when a user clicks on a Cancel button:

Private Sub cmdCancel_Click()

'click on the CANCEL button to close the form

'(could use ME instead - read on in the blog!)

Unload frmDrink

End Sub

When you double-click on the background of the form itself, you get the Click event for the form, which isn't that useful.

Choosing Controls and Events to Assign Code to

Rather than relying on double-clicking on a control to assign code to it, you can use the code window.  Here, for example, we assign code to the double-click event for the Cancel button:

List of controls on a form List of events for a control
Choose the control on the left ... ... then its event on the right.

You could write code in the resulting event-handler (that's what the macro which react to events are called) as follows:

Private Sub cmdCancel_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

'cancel whatever double-clicking would normally do, and display message instead

Cancel = True

MsgBox "Double-clicking is only supported in the paid version."

End Sub

Note that in this case there wouldn't be much point, as I can't double-click without first clicking, and I've set clicking on the Cancel button to close the form!

Moving between Code and Form

You can double-click on a form to return to its design, or right-click:

View object short-cut menu

To edit a form, right-click on the form name and choose to view it as an object, or just double-click on the form name.


However, the easiest way to move between a form and its code is probably to press one of the following keys:

Key What it does
F7 To go to code view from a form's design
SHIFT + F7 To return to a form from its code

Using Me to Refer to a Form

When you're writing code, you'll often want to refer to the current form.  You can do this in several ways, but the easiest is as Me.

In VBA Me always refers to the current object within which you're writing code, be it the Excel workbook, Access form or report, Word document or PowerPoint presentation ... or current user form.

So the code attached to our Cancel button could read:

Private Sub cmdCancel_Click()

'close the current form

Unload Me

End Sub

However, the biggest advantage of using Me is that it enables autocompletion:

Autocompletion for Me

When you type me., the user forms application gives a list of all of the controls on the form.  This makes typing code easier and safer.


A Typical Example - the Order Button

For our example, when a user clicks on the Order button we want to transfer the name and drink ordered for the person into a spreadsheet:

A person and drink added

The results if Kylie orders a Tea.


Here is some code which would do this (note that for the moment we assume the person filled in the form correctly - in the next part of this blog we'll look at validating form data):

Private Sub cmdOrder_Click()

'transfer to spreadsheet


'go to first blank cell



ActiveCell.Offset(1, 0).Select

ActiveCell.Value = Me.txtName.Text

ActiveCell.Offset(0, 1).Value = Me.txtDrink.Text

MsgBox "Drink added!"

'hide form

Unload Me

End Sub

What this macro does is:

  1. finds the first blank cell;
  2. sets its value to be the text typed into the txtName textbox; and
  3. sets the value of the cell to its right to be the text typed into the txtDrink textbox.

Finally, the macro unloads the form, thereby closing it.


As I mentioned, it's time now to look at validating data - what happens when your users aren't perfectly behaved.


  1. Coding Forms - the Basics
  2. Form and Control Events (this blog)
  3. Validating Form Data
This blog has 0 threads Add post