Writing VBA macros to work with user forms
Part one 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 (this blog)
  2. Form and Control Events
  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.

Coding Forms - the Basics

It's all very well drawing a pretty user form, but you then need to get it to interact with Excel!  That's what this blog is all about.  You can also read:

Running a User Form from the Code Window

Seeing what a form will look like is easy: just click on the form background to activate it first, then press the F5 key or click on the Run tool:

The VBA play tool

Here we're about to click on the green triangular play tool to see what the form looks like.

 

Unless you've attached code to any of the buttons, the only way to close down your form will then be to click on the cross at the top right:

A form's close button

In an ideal world the Cancel button would work too!

 

Running a Form from a Macro

A more likely scenario is that you'd want a user to run your form when they open a workbook or click on a button:

Spreadsheet with order button

You can attach a macro to this button so that when a user clicks on it, your form appears.

 

To reach this situation you need to write a macro in a separate module.  Here's how this could read:

Option Explicit

'the macro can be called anything you like

Sub ShowForm()

'this command would load the form, but not show it on screen

Load frmDrink

'this command would load and show the form

frmDrink.Show

End Sub

This macro has to be in a separate module because the ShowForm procedure above has to be publicly exposed.  Routines that you write within a form are private to that form, and nothing else can see them.

The Difference between Show/Hide and Load/Unload

The order of events for a form is as follows:

Order of form events

The order of events for a form is shown here.

The main reason that the order of events above is important is that the Initialize event for a form will only run when it loads: so if you hide and then redisplay a user form, any code attached to the Initialize event won't run.

Now we've seen how to run a form, it's time to look at getting the buttons to work - and for that we need to look at form events.

 

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