563 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
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).
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:
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!
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:
'the macro can be called anything you like
'this command would load the form, but not show it on screen
'this command would load and show the form
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 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.
|Parts of this blog|
25 Aytoun Street