BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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).
- Coding Forms - the Basics (this blog)
- Form and Control Events
- 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:
- How to draw user forms
- How to create and code some advanced controls
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:
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:
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:
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:
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.
- Coding Forms - the Basics (this blog)
- Form and Control Events
- Validating Form Data