560 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 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).
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 first thing you need to do to code a form is to make sure the bits you want to code have got sensible names:
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 the Order command button is shown here (you just change the Name property, which appears at the top of the list of properties).
The easiest way to attach code to an event for a control is to double-click on it:
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):
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!)
When you double-click on the background of the form itself, you get the Click event for the form, which isn't that useful.
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:
|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."
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!
You can double-click on a form to return to its design, or right-click:
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|
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
However, the biggest advantage of using Me is that it enables autocompletion:
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.
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:
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.Value = Me.txtName.Text
ActiveCell.Offset(0, 1).Value = Me.txtDrink.Text
MsgBox "Drink added!"
What this macro does is:
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.
|Parts of this blog|
25 Aytoun Street