The Excel VBA UserForm allows you to create a new Excel custom Window with select Form or ActiveX controls such a Button, ListBox, CheckBox and other controls. You can Show or Hide the UserForm and customize it as needed.
Below you will find a complete tutorial on how to create and customize your own Excel VBA UserForm.
Create VBA UserForm
User Forms fill the gap where a Message Box won’t do. UserForms allow you to create custom forms with Buttons, Combo-boxes, Scrollbars and many more controls.
Open the VBA Project to add a new UserForm
A new UserForm will be inserted into your VBA Project under a new branch called Forms.
Open the new UserForm and the Toolbox to view controls
Let’s create a simple form. First we need to see what form controls can we add to it. To view the Toolbox go to the View menu and select Toolbox:
Add Button to UserForm
Now let’s add a simple button to our new form. To do that we simply need to drag it from the Toolbox. Hit the Button icon and drag it to the new form:
To add code to your Button – double-click the button on the UserForm. This will navigate you to the code section of the UserForm. You can also go to the View code section as shown below:
This should create the following code stub:
1 2 3 | Private Sub CommandButton1_Click()End Sub |
Let’s just add a simple MessageBox to the new form to see that the button works:
1 2 3 | Private Sub CommandButton1_Click() MsgBox "Hello this is CommandButton1!"End Sub |
Show / Hide User Form
Now as we have created our new UserForm let’s put it to the test. Below a simple example of how to execute your UserForm:
1 2 3 4 5 | Sub TestUserForm() Dim uf as UserForm1 Set uf = New UserForm1 uf.Show 'Will make the UserForm VisibleEnd Sub |
Try clicking on the button to see that it works!
If you want to close the VBA UserForm use the Hide procedure. I replaced the contents of the Button Click procedure above as per below:
1 2 3 | Private Sub CommandButton1_Click() uf.Hide 'Will close / hide the UserFormEnd Sub |
UserForm Events
VBA Events are procedures that run automatically when something happens. VBA UserForm events are similar to VBA Worksheet or VBA Workbook events. The following is a list of available VBA UserForm events for Excel:
| UserForm Event | Description |
|---|---|
| Activate | VBA UserForm is activated (is front facing application against other forms or Excel windows) |
| AddControl | A Control is added to the VBA UserForm at run time |
| BeforeDragOver | A Mouse Drag and Drop operation is in progress (before drop) |
| BeforeDropOrPaste | Left click is released and data is dropped and pasted |
| Click | Left click on the VBA UserForm (not a control) |
| DblClick | Double left click on the VBA UserForm (not on a control) |
| Deactivate | UserForm loses focus |
| Initialize | Runs when the UserForm is created (to initialize the form e.g. add items to lists etc.) |
| KeyDown | Any keyboard button is pressed while the VBA UserForm is Active |
| KeyPress | An ANSI key is pressed when VBA UserForm is Active |
| KeyUp | Any keyboard button key is released while VBA Userform is active |
| Layout | Size of the UserForm was changed at run time |
| MouseDown | Any mouse button is pressed on the VBA UserForm (not a control) |
| MouseMove | Mouse is moved over the VBA UserForm |
| MouseUp | Mouse is released over the VBA UserForm |
| QueryClose | VBA UserForm is closed or memory released |
| RemoveControl | A control is removed from VBA UserForm at run time |
| Resize | VBA UserForm is resized |
| Scroll | VBA UserForm is scrolled |
| Terminate | VBA UserForm is removed from memory |
| Zoom | Occurs when VBA UserForm is zoomed |
Adding UserForm Events
To add Events to your UserForm use the drop-downs in the VBA Module of the Form:
Below are examples UserForm events:
1 2 3 4 5 6 7 | Private Sub UserForm_Click() Debug.Print "User Form was clicked!"End SubPrivate Sub UserForm_Initialize() Debug.Print "User Form was created!"End Sub |
