 |
|
|
|
Workbook
Download
This is a zipped Excel Workbook to go with this lesson.
UserForms - Introduction
UserForms
were first introduced into Excel in Excel 97. Prior to this it was Dialog sheets
that were used. These are still available by right clicking on any Worksheet
name tab and selecting Insert>MS Excel 5.0 Dialog. This will insert a Dialog
sheet that has a Dialog box on it and by default the Forms toolbar will appear.
However, they are only available for backward compatibility with previous
versions of Excel and should only be used for this reason. The UserForms that
are now available provide much greater flexibility for both the user and
developer. The UserForm is still at times referred to as a Dialog box.
Perhaps
the best result of the introduction of UserForms is not so much the Form itself
but the ability for it to accept ActiveX Controls. These Controls provide far
more flexibility than the Controls available on the Forms toolbar (OLE custom
controls). They have far superior Event handling that allows us to respond to a
users actions in ways that were previously not possible. By this I mean we can
have specific code run when the user clicks the control, enters, exits, double
clicks, right clicks, types and much more. Basically we are able to capture any
action that the user takes.
The
UserForm, like most things in Excel, is an *Object and as so is a Member of the
UserForms **Collection.
From
Excels help....
*Object
A
combination of code and data that can be treated as a unit, for example, a
control, form, or application component. Each object is defined by a class.
**Collection
An
object that contains a set of related objects. An object's position in the
collection can change whenever a change occurs in the collection; therefore, the
position of any specific object in the collection can vary.
.....End
of Excels help.
For more detail see: UserForm Object, UserForms Collection in the VBE help.
Private Module
As the UserForm is a Object (similar to a Worksheet Object) it has it's own Private Module. Within this Private Module we can use anyone of the UserForms Events. These Events are listed below:
As you can see many of the UserForm Events take arguments, for example in the: UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) we can use the Cancel argument to prevent a user from closing a UserForm via the X in the top right of the UserForm. To do this we would also need to use the CloseMode argument
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = 0 Then Cancel = True
End Sub
This simple bit of code would prevent the user from exiting the UserForm via the X, just ensure that you have a Cancel button on your UserForm! Then Cancel argument takes an Integer as it's argument. In case you are not familiar with True and False in the Visual Basic environment, False is equal to 0 (zero) and True is equal any other number. The other question that may spring to mind is just how did we no that a CloseMode of 0 means they used the X to close? Simply answer here is a message box.
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
MsgBox CloseMode
End Sub
Place this code into a UserForms Private module, run the Form and close via the X and we have our answer! In fact this is a handy way to find out many of Excel's UserForm return arguments.