---

 

---

Workbook Download
This is a zipped Excel Workbook to go with this lesson.

 

To purchase all 10 lessons and the 5 Workbooks for immediate download, click here

 

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:

---

UserForm_Activate()

 

UserForm_AddControl(ByVal Control As MSForms.Control)

 

UserForm_BeforeDragOver(ByVal Cancel As MSForms.ReturnBoolean, ByVal Control As MSForms.Control, ByVal Data As MSForms.DataObject, ByVal X As Single, ByVal Y As Single, ByVal State As MSForms.fmDragState, ByVal Effect As MSForms.ReturnEffect, ByVal Shift As Integer)

 

UserForm_BeforeDropOrPaste(ByVal Cancel As MSForms.ReturnBoolean, ByVal Control As MSForms.Control, ByVal Action As MSForms.fmAction, ByVal Data As MSForms.DataObject, ByVal X As Single, ByVal Y As Single, ByVal Effect As MSForms.ReturnEffect, ByVal Shift As Integer)

 

UserForm_Click()

 

UserForm_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

 

UserForm_Deactivate()

 

UserForm_Initialize()

 

UserForm_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

 

UserForm_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

 

UserForm_Layout()

 

UserForm_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)

 

UserForm_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)

 

UserForm_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)

 

UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

 

UserForm_RemoveControl(ByVal Control As MSForms.Control)

 

UserForm_Resize()

 

UserForm_Scroll(ByVal ActionX As MSForms.fmScrollAction, ByVal ActionY As MSForms.fmScrollAction, ByVal RequestDx As Single, ByVal RequestDy As Single, ByVal ActualDx As MSForms.ReturnSingle, ByVal ActualDy As MSForms.ReturnSingle)

 

UserForm_Terminate()

 

UserForm_Zoom(Percent As Integer)

---

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.

 

 

 

What to Use a UserForm For?

 

Again there is not really a definitive answer to this question. Basically they are used for collecting data from a user. They are possibly best suited for controlling the data that we want to collect from the user. We can do this by inserting code into the Private Module of the UserForm that validates what the user can enter. Not only do they allow us to control what the user can enter but we can then transfer this information onto a spreadsheet in a manner that ensures that data is entered in the correct place, format and manner. When the UserForm is used correctly we can make it virtually impossible for erroneous data to be entered, while at the same time making the inputting for the user very easy indeed.

 

UserForms and their associated Controls can be seen extensively throughout Excel. Activate any menu item that displays a Dialog box and we are looking at one. This can be a useful way for us to try and decide if a UserForm is suited to the task at hand. It can also be used to guide us in which Control to use in a particular situation.

 

As I have mentioned above, as you become more comfortable with UserForms and their Controls, you will start to use them for a variety of cases for tasks as simple as instructing a User through to creating your own Wizards that will guide the user through a series of steps.

 

Controls and the Toolbox

 

While most of what I have said above mentions the term UserForms, it is the Controls that are used in conjunction with the UserForm that supply the real 'guts' or workings. The UserForm itself is often only used to house our Controls, although it can do a lot more.

 

The Controls for a UserForm can be found on what is known as the "Toolbox". When we Insert a UserForm from within the VBE (Visual Basic Environment) via Insert>UserForm the Toolbox will be displayed by default. Or we can go to View>Toolbox. The Toolbox contains a single page tab aptly called "Controls". It is here that we will see all the Visual Basic controls plus any ActiveX Controls that may have been added. By default there will be fifteen Controls available these are:

 

1. Label

2. TextBox

3. ComboBox

4. ListBox

5. CheckBox

6. OptionButton

7. ToggleButton

8. Frame

9. CommandButton

10. TabStrip

11. MultiPage

12. ScrollBar

13. SpinButton

14. Image

15. Select Objects

 

These are certainly not the only Controls available to us, we can see the complete list by right clicking on any Control and selecting "Additional Controls". However the Controls mentioned above will no doubt provide us with more than enough flexibility.

 

The last Control mentioned (Select Objects) is not like any of the other Controls as it cannot be placed (drawn) on the UserForm. It only purpose is to allow us to move or resize a Control that has been placed on the UserForm.

 

The Toolbox also allows us to create a 'Template' of controls that have already been added to a UserForm and had some or all of their Properties changed. To do this we right click on the "Control" page tab and select "New Page". We can then drag onto this page any Controls that we have altered and have the changes stay. This comes in very handy if you are always using needing to change the Properties of a Control.

 

The UserForm

We will now move on to the UserForm itself

The UserForm like any other Control has its own Properties, which can be seen by right clicking on the form and selecting Properties from the pop-up menu.  There are two tabs in the Properties Window labelled "Alphabetic" and "Categorized".  There is no difference between the two tabs except the order in which they are listed.  You will also notice in the Properties Window there is a drop down box which will contain the names of all your Controls that are attached to the UserForm.

The Events for a UserForm can be easily accessed by double clicking on the UserForm, which will immediately take you to the Private Module of the UserForm itself.  You will also notice that the default Procedure or Event for a UserForm is the Click Event.  This is the case with most Controls.  To see the complete list of Events for the UserForm, ensure your mouse insertion point is anywhere within the UserForm_Click Event and then select the drop arrow in the Procedure Window (top right of your screen).

The very first thing you should do when you initially start to design a UserForm is to change its name from the default UserForm1 to a meaningful name.  You would do this in the Properties Window.

Another very good practice, which we will use throughout the lesson is the use of  the Keyword "Me" to refer to the UserForm.  The word "Me" will always refer to the UserForm whose Private Module it is placed within.  One of the reasons why this is good practice is that if the name of the UserForm ever changes, the keyword "Me" will still apply.

Another method for accessing the UserForms Properties is to double click it (to put you in the Private Module of the UserForm).  You could then simply type "Me" followed immediately by a period (full-stop) and Excel will automatically display all Properties for the UserForm.  Always select your Properties from this display list rather than typing to eliminate human error.  It is also good practice that when you type any code that you use lowercase, this way you will know immediately if you have the syntax spelling etc., correct as Excel will automatically capitalize the letters of all recognised words as soon as you move to the next line.

In case you did not realise, you can access the help for any key words or terms by placing your mouse insertion point anywhere within the word and pushing F1 this will automatically jump you to the help topic for that specific word of phrase.

Show and Load

The method used to launch a UserForm can be a CommandButton placed on a Worksheet, a Custom menu bar, the standard menu bar, shortcut key etc. The method we will assume here is via use of a CommandButton placed on a Worksheet. To achieve this we would go to View> Toolbar>Control Toolbox and place a CommandButton onto a Worksheet. We would then double click the CommandButton to have Excel take us straight to the Click Event of the CommandButton. It is here we would place

This is the simplest method to load and show a UserForm.  By load, I mean load into Excel's memory.  It is important to note here, that this is one of the few instances that you cannot refer to the UserForm with the key word "Me".  The reason for this is the code for the CommandButton placed on a Worksheet does not and cannot reside in the Private Module for the UserForm itself.  If you did use the keyword "Me", you would be referring to the Worksheet Object and not the UserForm Object. This is simply because, the CommandButton on a Worksheet is attached to the Worksheet Object as opposed to the UserForm Object.

The opposite of UserForm1.Show, would be UserForm1.Hide.  But there is one very important difference.  This is that while UserForm1.Show will automatically load the UserForm into memory,  UserForm1.Hide will not unload it from memory.  To unload the UserForm from memory, you must use the line of code: "Unload UserForm1".  In most, if not all, cases you will use "Unload UserForm1".  The only instance you would use UserForm1.Hide would be if your UserForm was extremely complex and took a long time to load.  This way you would leave it in memory so that it could be shown again quickly. This would basically mean you would only have to load the UserForm once. The other time you may use UserForm1.Hide as instead of Unload UserForm1 would be when you wanted all the Controls to retain any information that had been added. When you unload a UserForm all Controls will go back to their default settings, while hiding it will retain all current values and settings.

The opposite to "Unload UserForm1" is "Load UserForm1".  This will load your UserForm into memory, but will not make it visible.  Again, as above, you would probably only use this if your UserForm was very complex.

UserForms Order of Events

The first Event that will fire when you either "Show" or "Load" your UserForm is the "Initialize" Event.  This will occur immediately after the UserForm is loaded, but before it is shown (visible). It is important to understand that, if the UserForm was already Loaded (but not visible) the Initialize Event would not fire by using the Show Method. This is because the UserForm would already be loaded into memory. This means that the Show Method will Load a UserForm, if it's not already, but will only make it visible if already loaded.

The next Event that will fire is the "Activate" event.  It is important that you know the difference between "Initialize" and "Activate".  While "Activate" will occur if you "hide" then re-show a UserForm, the "Initialize" Event will not.  This is because the UserForm has not been unloaded from memory. So this means that the Initialize Event will only fire when the UserForm is loaded into memory, while the Activate Event will fire whenever the UserForm is made visible. The order of any Events after this is dependant on the action taken by the user.

Focus

The term Focus is described by Excel as:

The ability to receive mouse clicks or keyboard input at any one time. In the Microsoft Windows environment, only one window, form, or control can have this ability at a time. The object that "has the focus" is normally indicated by a highlighted caption or title bar. The focus can be set by the user or by the application.

In Excel 2000 UserForms now have a new Property called "ShowModal".  This is a Boolean Property as it can either be set to True or False with the default being True.  If the Property is left set as "True", the user will not be able to shift Focus to any other Objects except the Controls of the UserForm itself.  This means that while the UserForm is visible, the user will not be able to select cells on a Worksheet.  If the Property is set to "False", the User will be able to select cells and operate menu bar options on a Worksheet.  When designing UserForms for other users, I would strongly recommend leaving the default as "True" otherwise the user will be able to make changes that you have no control over.

As a simple exercise to get you acquainted with UserForms I would like you to insert a UserForm as a shell and code it so that it is Loaded into memory and visible by clicking a CommandButton that is attached to a Worksheet I would also like you to give your UserForm a meaningful name and set up a "Cancel" button so that your form is unloaded from memory when this button is clicked.

I will keep this first lesson simple to allow you to ask any questions relating to what we have discussed.

Please find attached a Workbook with four buttons on a Worksheet that will hopefully help you understand the Events and use of Keywords we have discussed. After using each button take a look at the code for each.

I will let you digest this for any period that you see fit and ask any questions that you need to in your next e:mail. 

---

 

 

---

 

---