Excel Training VBA 2 Lesson 3
UserForm Controls & The Control Toolbox
Workbook Download
This is a zipped Excel Workbook to go with this lesson.
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:
Label
TextBox
ComboBox
ListBox
CheckBox
OptionButton
ToggleButton
Frame
CommandButton
TabStrip
MultiPage
ScrollBar
SpinButton
Image
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
Private Sub CommandButton1_Click() UserForm1.Show
End Sub
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.
