excel vba userform

Excel VBA UserForm – Complete Excel UserForm Tutorial

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

Excel VBA Tutorial: Inserting a new UserForm
Inserting 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:

Excel VBA Tutorial: Open the UserForm Toolbox
Open the UserForm 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:

Add a Button to your UserForm
Add a Button to your UserForm

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:

Excel VBA Tutorial: View code on the UserForm
View code on the UserForm

This should create the following code stub:

Private Sub CommandButton1_Click()

End Sub

Let’s just add a simple MessageBox to the new form to see that the button works:

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:

Sub TestUserForm()
 Dim uf as UserForm1
 Set uf = New UserForm1
 uf.Show 'Will make the UserForm Visible
End 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:

Private Sub CommandButton1_Click()
 uf.Hide 'Will close / hide the UserForm
End 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:
vba userform events
Below are examples UserForm events:

Private Sub UserForm_Click()
  Debug.Print "User Form was clicked!"
End Sub

Private Sub UserForm_Initialize()
  Debug.Print "User Form was created!"
End Sub