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
Search our website
We also send out useful tips in a monthly email newsletter ...
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).
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.
|
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:
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 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 |
---|
|
Some other pages relevant to the above blogs include:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2023. All Rights Reserved.