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()
Unload frmDrink
End Sub
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 = True
MsgBox "Double-clicking is only supported in the paid version."
End Sub
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()
Unload Me
End Sub
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()
Worksheets("Orders").Select
Cells.Find("Person").Select
ActiveCell.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = Me.txtName.Text
ActiveCell.Offset(0, 1).Value = Me.txtDrink.Text
MsgBox "Drink added!"
Unload Me
End Sub
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.
