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).
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.
Form and Control Events
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 Importance of Naming Controls
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).
Attaching Code to Events by Double-Clicking
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.
Choosing Controls and Events to Assign Code to
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!
Moving between Code and 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|
Using Me to Refer to a Form
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.
A Typical Example - the Order Button
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:
- finds the first blank cell;
- sets its value to be the text typed into the txtName textbox; and
- sets the value of the cell to its right to be the text typed into the txtDrink textbox.
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.