Program by Levels
This page is one of the 33 lessons from the
Tutorial on VBA for Excel (Macros)
VBA for Excel made simple
VBA Lesson 3-2: Userforms Properties and VBA Code
(Levels: Intermediate)
The UserForm Properties
As you have seen in lesson 1-13 on the Visual Basic Editor you double click on the userform's name in the Project window and its properties appear in the Properties window:
In the Properties window of the VBE you MUST change the name "(Name)" of the form, its caption (the name in the blue band at the top of the UserForm) and you can also modify the default setting of any of the 32 other properties of the form.
When you name a form always use the prefix "frm" like in "frmDatabase" and be as descriptive as you can be so that your code will be easy to read. Always use one or more upper case letters in the name. When you write "frmdatabase.show" in lower case letters Excel will capitalize some letters "frmDatabase.Show" letting you know that the name is spelled correctly.
The caption is what your users will se at the top of the userform. Be as informative as possible and complete the information with a label if necessary.
Else than the Name and Caption there are just a few properties that you might want to modify. You can select a different color for the background with the property "BackColor" .
By default the userform appears in the center of the screen. If you want it to show somewhere else set the "Start" property to "0-Manual" and use the "Top" and "Left" properties to set a new position.
The Code
Opening and Closing the Userform
The first thing to do is to create code to call your userform. Here is a basic line of code doing so:
frmCity.Show
See lesson 1-9 on Events to learn how to assign macros to a text box or other button that you place on the sheet.
The line of code to close the userform is:
frmCity.Hide or Me.Hide "Me" being the general name of the active form
The "Hide" sentence is usually part of the code of a command button on the form. A user clicks on a "GO" , "SEND, "SUBMIT" or "CANCEL" button and part of what must happen is that the userform disappears from the screen.
Resetting the controls
When you enter values in the controls of a userform (text boxes, option buttons, etc.) they don't go away when you close the form so they are back when you reopen it.
When the form opens or closes the value of some controls might need to be reset or simply set. You can do this within the "Activate" event of the userform or also within the "Deactivate" event. But if you chose to do it at closing you must also do it within the "Terminate" event when the user clicks on the "X" at the top right of the form.
So let's do it at the opening. Double click on the gray part of the form in the code window. Select the event "Activate" in the drop down list at the top right of the code window. VBA creates the first and last line of the procedure. Add your code like:
tbxSoAndSo.Value="" or tbxSoAndSo.Value=0
cbxSoAndSo.value="Choose a value" or cbxSoAndSo.value=""
If you get into more complex programming you can make certain controls invisible at opening:
tbxSoAndSo.Vsible= False
You will make them come back when the user makes a certain selection in another control by adding a line of code within the " Change" event of that control.
tbxSoAndSo.Vsible= True
You can add code within any of the 22 events of the userform but usually the " Activate" event is the only one that you will use.
This page is one of the 33 lessons from the
Tutorial on VBA for Excel (Macros)
VBA for Excel made simple