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:

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:

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