If you have programmed with AutoLisp and the Dialogue Control Language (DCL), you will be familiar with designing dialogue boxes. Compared to DCL though, designing dialogue boxes in VBA is a breeze. The basis of all dialogue boxes in VBA is the UserForm. This Tutorial will show you how to display and manipulate UserForms.
A UserForm is a container that holds all the controls such as labels,
textboxes, pictures, etc. that make up part of your applications interface.
A UserForm has it's own Properties, Methods and Events. Let's have a look at some of them :
Displaying a UserForm
The syntax for displaying a UserForm is as follows :
So, to display a UserForm named UserForm1, you would use the following code :
If you want to, you can preload the UserForm into memory without actually displaying it. This can be useful as it can sometimes take a few seconds for a complex UserForm to appear. The ability to preload the UserForm allows you to decide when you would like this operation to take place. To preload a UserForm you would use the following code :
Hiding/Unloading a UserForm
To temporarily hide a UserForm, you would use the Hide method. This is a very good example of how dialogue boxes in VBA are so much simpler than in AutoLisp. To hide a UserForm you would use the following code:
To Unload a UserForm from memory use the following code :
You could also use the "Me" keyword :
UserForms support many predefined events. Among the most commonly used events are Initiliaze, Click and Terminate events.
Note: A VBA module that contains an event procedure can be called a module behind the UserForm. A module that contains event procedures is not visible in the Modules collection of the Projects window of the VBA Editor. You must double-click the body of the UserForm to view the UserForm Code Module.
Let's have a look at some UserForm Events. Start your VBA Editor and insert a UserForm into a new Project. Double-Click the UserForm and type in the following code :
Private Sub UserForm_Click() Me.Height = Int(Rnd * 500) Me.Width = Int(Rnd * 750) End Sub Private Sub UserForm_Initialize() Me.Caption = "UserForm Events" Me.BackColor = RGB(10, 25, 100) End Sub Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) msg = "Now Unloading " & Me.Caption MsgBox prompt:=msg, Title:="QueryClose Event" End Sub Private Sub UserForm_Resize() msg = "Width: " & Me.Width & Chr(10) & "Height : " & Me.Height MsgBox prompt:=msg, Title:="Resizing Event" End Sub Private Sub UserForm_Terminate() msg = "Now Unloading " & Me.Caption MsgBox prompt:=msg, Title:="Terminate Event" End Sub
Now run the UserForm. This is what happens when you run the project:
Firstly, The Intialize event procedure changes the Caption property to "UserForm Events" and the Backcolor property to dark Blue.
When you click the UserForm the Click Event procedure is initiated and the UserForm is re-sized. Also, because you created a resize event procedure, you receive 2 message boxes. The resize event occurs twice because your code behind the click event changed both the Width and Height properties of the UserForm.
When you close the UserForm, the QueryClose event procedure is triggered. This displays a message box with the caption you gave the UserForm in the code for the Initialize event. The QueryClose event is useful when you want to perform a certain set of actions when the UserForm is closed by the user.
The Terminate Event then triggers a message box which states that the Caption of the UserForm is UserForm1. The Terminate Event occurs after the UserForm is removed from memory and the Caption of the UserForm returns to it's original state.