VBA UserForm – A Guide for Everyone

“The system should treat all user input as sacred.” – Jef Raskin


A Quick Guide to the VBA UserForm

The following table provides a quick guide to the most common features of the UserForm

Declare and create Dim form As New userformCars
Declare and create Dim form As userformCars
Set form = New userformCars
Show as modalform.Show
form.Show vbModal
Show as non modalform.Show vbModeless
UnloadPrivate Sub buttonCancel_Click()
  Unload Me
End Sub
HidePrivate Sub buttonCancel_Click()
End Sub
Get\set the titleform.Caption = "Car Details"



The VBA UserForm is a very useful tool. It provides a practical way for your application to get information from the user.

If you are new to UserForms you may be overwhelmed by the amount of information about them. As with most topics in VBA, 90% of the time you will only need 10% of the functionality.

In these two blog posts(part 2 is here) I will show you how to quickly and easily add a UserForm to your application.

This first post covers creating the VBA Userform and using it as modal or modeless. I will also show you how to easily pass the users selection back to the calling procedure.

In the second part of this post I will cover the main controls such as the ListBox, the ComboBox(also called the Dropdown menu), the TextBox and the CheckBox. This post will contain a ton of examples showing how to use each of these controls.


Useful Resources

If you are looking for more online information about the VBA UserForm you can go to

MSDN – Microsoft Developer Network – UserForms

Another great resource is John Walkenbach’s VBA book. It has an entire section(150 pages) dedicated to UserForms and is well worth reading

Excel 2013 Power Programming with VBA by John Walkenbach


What are VBA Userforms?

A UserForm is a dialog which allows your application to get input from the user. UserForms are used throughout all Windows applications. Excel itself has a large number of UserForms such as the Format Cells UserForm shown in the screenshot below.


VBA Userform

Excel’s “Format cells” UserForm

UserForms contain different types of controls such as Buttons, ListBoxes, ComboBoxes(Dropdown lists), CheckBoxes and TextBoxes.

In the Format Cells screenshot above you can see examples of these controls:

  • Font, Font style and Size contain a textbox with a ListBox below it
  • Underline and Color use a Combobox
  • Effects uses three CheckBoxes
  • Ok and Cancel are command Buttons

There are other controls but these are the ones you will use most of the time.


Built in VBA Userforms

It is important to note that VBA has some useful built-in UserForms. These can be very useful and may save you having to create a custom one. Let’s start by having a look at the MsgBox.


VBA MsgBox

The VBA message box allows you to display a dialog to the user. You can choose from a collection of buttons such as Yes, No, Ok and Cancel.

VBA MsgBox

You can easily find out which of these buttons the user clicked on and use the results in your code.

The following code shows two simple examples of using a message box

Sub BasicMessage()

    ' Basic message
    MsgBox "There is no data on this worksheet "
    ' Basic message with "Error" as title
    MsgBox "There is no data on this worksheet ", , "Error"

End Sub


In the next example we ask the user to click Yes or No and print a message displaying which button was clicked

Sub MessagesYesNoWithResponse()

    ' Display Yes/No buttons and get response
    If MsgBox("Do you wish to continue? ", vbYesNo) = vbYes Then
        Debug.Print "The user clicked Yes"
        Debug.Print "The user clicked No"
    End If

End Sub


In the final example we ask the user to click Yes, No or Cancel

Sub MessagesYesNoCancel()

    ' Display Yes/No buttons and get response
    Dim vbResult As VbMsgBoxResult

    vbResult = MsgBox("Do you wish to continue? ", vbYesNoCancel)

    If vbResult = vbYes Then
        Debug.Print "The user clicked Yes"
    ElseIf vbResult = vbNo Then
        Debug.Print "The user clicked No"
        Debug.Print "The user clicked Cancel"
    End If

End Sub



If you want to get a single piece of text or value from the user you can use the InputBox. The following code asks the user for a name and writes it to the Immediate Window(Ctrl + G to view)

Sub GetValue()

    Dim sValue As String
    sValue = InputBox("Please enter your name", "Name Entry")
    Debug.Print sValue

End Sub



We can use the Windows file dialog to allow the user to select a file or multiple files.

The first example allows the user to select a file

' Print the name of the selected file
sfile = Application.GetOpenFilename("Excel Files (*.xlsx),*.xlsx")
Debug.Print sfile

This following example allows the user to select multiple files

Sub GetMultipleFiles()
    Dim arr As Variant
    arr = Application.GetOpenFilename("Text Files(*.txt),*.txt" _ 
        , MultiSelect:=True)

    ' Print all the selected filenames to the Immediate window
    Dim filename As Variant
    For Each filename In arr
        Debug.Print filename
End Sub


How to Create a VBA UserForm

If the built-in UserForms do not cover your needs then you will need to create your own custom Userform. To use a UserForm in our code we must first create one. We then add the necessary controls to this userform.

We create a UserForm with the following steps

  1. Open the Visual Basic Editor(Alt + F11 from Excel)
  2. Go to the Project Window which is normally on the left(select View->Project Explorer if it’s not visible)
  3. Right-click on the workbook you wish to use
  4. Select Insert and then UserForm(see screenshot below)


VBA Userform Create

Creating a Userform

A newly created UserForm will appear. Anytime you want to access this Userform you can double click on the UserForm name in the Project window.

The Toolbox dialog should also be visible. If it’s not visible select View->Toolbox from the menu. We use the toolbox too add controls to our UserForm.

VBA Toolbox

The UserForm Toolbox


Designing the UserForm

To view the design of the UserForm, double click on it in the Project window. There are three important windows we use when creating our UserForms.

  1. The UserForm
  2. The properties window – this is where we can change the setting of the Userform and its controls
  3. The toolbox – we use this to add new controls to our UserForm


VBA UserForm

UserForm Windows


A Very Simple UserForm Example

Let’s have a look at a very simple user form example.

  1. Create a new UserForm
  2. Rename it to userformTest in the (Name) property in the properties window
  3. Create a new module(Right click on properties window and select Insert->Module)
  4. Copy the DislayUserForm sub below below to the module
  5. Run the sub using Run->Run UserForm Sub from the menu
  6. The UserForm will be displayed – you have created your first UserForm application!
  7. Click on the X in the top right of the UserForm to close


[sourcecode languague=”vb”]
Sub DisplayUserForm()

Dim form As New UserFormTest

End Sub


Setting the Properties of the UserForm

We can change the attributes of the UserForm using the properties window. Select View->Properties Window if the window is not visible.

When we click on the UserForm or a control on a UserForm then the Properties window displays the attributes of that item.

VBA Properties Window

VBA Properties Window

Generally speaking, you only use a few of these properties. The important ones for the UserForm are Name and Caption.

To change the name of the UserForm do the following

  1. Click on the UserForm in the Project window or click on the UserForm itself
  2. Click in the name field of the properties window
  3. Type in the new name


The Controls

We add controls to the UserForms to allow the user to make selections, enter text or click a button. To add a control use the steps below

  1. Go to the toolbox dialog – if not visible select View->Toolbox
  2. Click on the control you want to add – the button for this control will appear flat
  3. Put the cursor over the UserForm
  4. Hold down the left mouse button and drag until the size you want

The following table shows a list of the common controls

CheckBoxTurn item on/off
ComboBoxAllows selection from a list of items
CommandButtonClick to perform action
Label Displays text
ListBoxAllows selection from a list of items
TextboxAllows text entry


Adding the Code

To view the code of the UserForm

  1. Right click on the UserForm in the properties windows(or the UserForm itself) and select “View Code”
  2. You will see a sub called UserForm_Click. You can delete this when you create your first sub

Note: If you double click on a control it will bring you to the click event of that control. This can be a quicker way to get to the UserForm code.


Adding Events

When we use a UserForm we are dealing with events. What this means is that we want to perform actions when events occur. An event occurs when the users clicks a button, changes text, selects an item in a ComboBox etc. We add a Sub for a particular event and place our code in it. When the event occurs our code will run.

One common event is the Initialize event  which occurs when the UserForm is created at run time. We normally use this event to fill our controls with any necessary data. We will look at this event in the section below.

VBA Event combobox

To add an event we use the ComboBoxes over the code window(see screenshot above). The left one is used to select the control and the right one is used to select the event. When we select the event it will automatically add this sub to our UserForm module.

Note: Clicking on any control on the UserForm will create the click event for that control.


The Initialize Event

The first thing we want to do with a UserForm is to fill the controls with values. For example, if we have a list of countries for the user to select from we could use this.

To do this we use the Initialize event. This is a sub that runs when the UserForm is created(see next section for more info).

To create the Initialize event we do the following

  1. Right click on the UserForm and select View Code from the menu.
  2. In the Dropdown list on the left above the main Window, select UserForm.
  3. This will create the UserForm_Click event. You can ignore this.
  4. In the Dropdown list on the right above the main Window, select Initialize.
  5. Optional: Delete the UserForm_Click sub created in step 2.


VBA Userform Initialize

Adding the Initialize Event


We can also create the Initialize event by copying or typing the following code

Private Sub UserForm_Initialize()

End Sub


Once we have the Initialize event created we can use it to add the starting values to our controls. We will see more about this in the second part of this post.


Initialize versus Activate

The UserForm also has an Activate event. It is important to understand the difference between this and the Initialize event.

The Initialize event occurs when the actual object is created. This means as soon as you use on of the properties or functions of the UserForm. The code example below demonstrates this

Dim frm As New UserForm1

' Initialize will run as UserForm is created 
' the first time we use it
frm.BackColor = rgbBlue


We normally reference the UserForm first by calling Show which makes it seem that displaying the UserForm is triggering the Initialize event. This is why there is often confusion over this event.

In the example below calling Show is the first time we use the UserForm. Therefore it is created at this time and the Initialize event is triggered.

Dim frm As New UserForm1

' Initialize will run here as the Show is the 
' first time we use the UserForm

The Activate event occurs when the UserForm is displayed. This can happen using Show. It also occurs any time the UserForm is displayed. For example, if we switch to a different window and then switch back to the UserForm then the Activate event will be triggered.

We create the Activate event the same way we create the Initialize event or we can just copy or type the following code

Private Sub UserForm_Activate()

End Sub


  1. Initialize occurs when the Userform is created. Activate occurs when the UserForm is displayed.
  2. For each UserForm you use – Initialize occurs only once, Activate occurs one or more times.


Calling the UserForm

We can use the UserForm in two ways

  1. Modal
  2. Modeless

Let’s look at each of these in turn.


Modal Userform

Modal means the user cannot interact with the parent application while this is visible. The excel Format cells dialog we looked at earlier is a modal UserForm. So are the Excel Colors and Name Manager dialogs.

We use modal when we don’t want the user to interact with any other part of the application until they are finished with the UserForm.


Modeless Userform

Modeless means the user can interact with other parts of the application while they are visible. An example of modeless forms in Excel is the Find dialog(Ctrl + F).

You may notice that any Excel dialog that allows the user to select a range has a limited type of Modeless – the user can select a range of cells but cannot do much else.


Modal versus Modeless

The actual code to make a UserForm modal or modeless is very simple. We determine which type we are using when we show the UserForm as the code below demonstrates

Dim frm As New UserFormFruit

' Show as modal - code waits here until UserForm is closed
frm.Show vbModal

' Show as modeless - code does not wait
frm.Show vbModeless

' default is modal

As the comments in the above indicate the code behaves differently for Modal and Modeless. For the former it waits for the UserForm to close and for the latter it continues on.

Even though we can display any UserForm as modal or modeless we normally use it in one way only. This is because how we use them is different


Typical use of a Modal form

With a Modal UserForm we normally have an Ok and a Cancel button.

VBA UserForm

The Ok button normally closes the UserForm and performs the main action. This could be saving the user inputs or passing them back to the procedure.

The Cancel button normally closes the UserForm and cancels any action that may have taken place. Any changes the user made on the UserForm are ignored.


Typical use of a Modeless form

With a Modeless UserForm we normally have a close button and an action button e.g. the Find button on the Excel Find Dialog.

When the action button is clicked an action takes place but the dialog remains open.

The Close button is used to close the dialog. It normally doesn’t do anything else.


A Modal Example

We are going to create a Modal UserForm example. It is very simple so you can see clearly how to use a UserForm.

The following UserForm allows the user to enter the name of a fruit

VBA Modal dialog example

We use the following code to show this UserForm and to retrieve the the contents of the fruit textbox

Sub UseModal()

    ' Create and show form
    Dim frm As New UserFormFruit

    ' Display Userform - The code in this procedure 
    ' will wait here until the form is closed
    ' Display the returned value
    MsgBox "The user has selected " & frm.Fruit
    ' Close the form
    Unload frm
    Set frm = Nothing

End Sub

' Returns the textbox value to the calling procedure
Public Property Get Fruit() As Variant
    Fruit = textboxFruit.Value
End Property

' Hide the UserForm when the user click Ok
Private Sub buttonOk_Click()
End Sub

What you will notice is that we hide the UserForm when the user clicks Ok. We don’t set it to Nothing or unload it until after we are finished retrieving the user input. If we Unload the UserForm when the user clicks Ok then it no longers exists so we cannot access the values we want.


Cancelling the UserForm

We always want to give the user the option to cancel the UserForm. Once it is cancelled we want to ignore any selections the user made.

Each form comes with an X in the top right-hand corner which allows the user to cancel it.

VBA Userform X

The X button on the UserForm

This button cancels the UserForm automatically – no code is necessary. When the user clicks X the UserForm is unloaded from memory. That is, it no longer exists so we will get an error if we try to access it. The code below will give an error if the user clicks on the X


Sub DisplayFruit()
    Dim frm As New UserFormFruit
    ' ERROR HERE - If user clicks the X button
    Debug.Print frm.Fruit
End Sub

VBA Automation Error

To avoid this error we want to prevent the UserForm being Unloaded when the X button is clicked. To do this we use the QueryClose event.


Private Sub UserForm_QueryClose(Cancel As Integer _
                                       , CloseMode As Integer)
    ' Prevent the form being unloaded
    If CloseMode = vbFormControlMenu Then Cancel = True
    ' Hide the Userform and set cancelled to true
    m_Cancelled = True
End Sub

In the first line we prevent the UserForm being unloaded. With the next lines we hide the UserForm and set the m_Cancelled variable to true. We will use this variable later to check if the UserForm was cancelled.

We can then update our calling procedure to check if the UserForm was cancelled

Sub DisplayFruit()
    Dim frm As New UserFormFruit
    If frm.Cancelled = False Then
        MsgBox "You entered: " & frm.Fruit
        MsgBox "The UserForm was cancelled."
    End If
End Sub

If we want to add a Cancel button it is simple to do. All we need to do is Hide the form and set the variable m_Cancelled to true. This is the same as we did in the QueryClose Event above.

Private Sub buttonCancel_Click()
    ' Hide the Userform and set cancelled to true
    m_Cancelled = True
End Sub

VBA Modal dialog example with Cancel


Using the Escape key to cancel

If you want to allow the user to cancel using the Esc it is simple(but not obvious) to do. You set the Cancel property of your ‘Cancel’ button to True. When Esc is pressed the click event of your Cancel button will be used.

VBA Cancel property


Putting All the Modal Code Together

The final code for a Modal form looks like this

Private m_Cancelled As Boolean

' Returns the cancelled value to the calling procedure
Public Property Get Cancelled() As Variant
    Cancelled = m_Cancelled
End Property

' Returns the textbox value to the calling procedure
Public Property Get Fruit() As Variant
    Fruit = textboxFruit.Value
End Property
Private Sub buttonCancel_Click()
    ' Hide the Userform and set cancelled to true
    m_Cancelled = True
End Sub

' Hide the UserForm when the user click Ok
Private Sub buttonOk_Click()
End Sub

' Handle user clicking on the X button
Private Sub UserForm_QueryClose(Cancel As Integer _
                                  , CloseMode As Integer)
    ' Prevent the form being unloaded
    If CloseMode = vbFormControlMenu Then Cancel = True
    ' Hide the Userform and set cancelled to true
    m_Cancelled = True
End Sub

Sub DisplayFruit()
    ' Create the UserForm
    Dim frm As New UserFormFruit
    ' Display the UserForm
    ' Check if the user cancelled the UserForm
    If frm.Cancelled = True Then
        MsgBox "The UserForm was cancelled."
        MsgBox "You entered: " & frm.Fruit
    End If
    ' Clean up
    Unload frm
    Set frm = Nothing
End Sub

You can use this code as a framework for most of your Modal UserForms.


How to Use a Modeless form

We are now going to use a simple example to show how to use a Modeless form. In this example we will add a customer name to a worksheet each time the clicks on the the Add Customer button.

VBA Modeless Userform example

The following code displays the UserForm. The important thing to notice here is that after the frm.Show line, the code will continue on. This is different to Modal where the code waits at this line for the UserForm to be closed or hidden.

Sub UseModeless()

    Dim frm As New UserFormCustomer
    ' Unlike the modal state the code will NOT
    ' wait here until the form is closed
    frm.Show vbModeless
End Sub

When the Add button is clicked the action occurs immediately. We add the customer name to a new row in our worksheet. We can add as many names as we like. The UserForm will remain visible until we click close.

Private Sub buttonAdd_Click()
End Sub

Private Sub buttonClose_Click()
    Unload Me
End Sub

Private Sub InsertRow()
    With Sheet1
        ' Get the current row
        Dim curRow As Long
        If .Range("A1") = "" Then
            curRow = 1
            curRow = .Range("A" & .Rows.Count).End(xlUp).Row + 1
        End If
        ' Add item
        .Cells(curRow, 1) = textboxFirstname.Value
        .Cells(curRow, 2) = textboxSurname.Value
    End With
End Sub


Part 2 of this post

You can find the second part of this post here.


What’s Next?

If you want to read about more VBA topics you can view a complete list of my posts here. I also have a free eBook(see below) which you will find useful if you are new to VBA.

If you are serious about mastering VBA then you may want to check out Build 11 Full VBA Applications


Get the Free eBook

How To Ace the 21 Most Common Questions in VBA

Please feel free to subscribe to my newsletter and get exclusive VBA content that you cannot find here on the blog, as well as free access to my eBook, How to Ace the 21 Most Common Questions in VBA which is full of examples you can use in your own code.

Free VBA eBook




  1. I guess the second, etc instance of a form automatically gets the name nameOfFirstInstance_1,nameOfFirstInstance_2,etc

    1. Hi Henry,

      No, they will have the same name. So using IsUserFormLoaded won’t check for the second one correctly.

      They code below will work for that situation

      ' Check if a given UserForm is currently loaded
      Function IsUserFormLoaded2(ByVal frm As UserForm) As String
          On Error Resume Next
          Dim s As String
          s = frm.Caption
          IsUserFormLoaded2 = s <> ""
      End Function
  2. Paul –

    As usual, a very clear and informative entry. I have recently designed a couple of “applications” for my employer in Excel VBA & based on those experiences, I have a humble suggestion for a topic to consider covering in part 2. I had a challenging time scouring the various Excel sites for info on how to use the info on a form if the user can make multiple choices; for example, once the user checks the desired boxes in a multi-select checkbox/listbox, how do I get those items and write them to a range?
    I did eventually find code to help me do this, and it’s working well, but I can’t honestly say I fully understand HOW it works. Also, I’m writing the values to a range in order to have other subs then loop through the range & use the values for other operations, and I keep thinking that this is actually slowing the application down overall. Excel already has the results in memory somewhere & there’s undoubtedly a way to set them up (maybe as a collection?) and use them for other functions without taking the time to actually write them first, but how?
    If you have room left in part 2, I’m sure many readers would benefit from your insight in this area.

    Thanks again for your willingness to help!

    1. Thanks very much for the feedback Michael, it is very welcome. I will definitely be including multi-select from listboxes in part 2 of this post.


  3. Hi Paul,
    Thanks for this clear approach on userforms. Whats new for me is the declaration of the userform. Its like the declaration of a Class. Does this mean that the way you define a userform, it can be approached like a class? New to me is also the way you use the Property Get in the userform. Does this mean that you can use the value of – let say – a textbox everywhere in the project? I was allways wondering how controls like textbox, listbox, … could be passed to an other sub. I’ve never seen this before and I’m also 15 years using VBA to make things easier and faster than manually. I’ll have still a lot to learn if i read your article. looking forward to see the next chapter on userforms.

    While you still need to post the next ‘chapter’, can you also explain the best value we give to a checkbox? I use allways the value of False or True, but i’m convinced there must be an other predefined variable to set/clear a checkbox, something like CheckBox1.Value = msFormsTrue
    Unfortunately there’s no Intellisense value popping up to guide us to the right value.

    Best regards,

    1. Hi Ludo,

      Thanks for your comment and the feedback.
      1. Yes, when you use an instance of a class in your code it is referred to as an object. So UserForm, Workbooks, worksheets etc. are all objects that have a class defined for them.
      2. You can use the value of the textbox everywhere. When the UserForm is closed you would normally store it to a variable.
      3. True/False is fine to use for the checkbox.

  4. Hi Paul. This is a great site and I really appreciate your willingness to help others. In the modal form I wanted to set a default value for the fruit (say banana) and then have it highlighted so the user could accept it or just start typing. How would you go about accomplishing that?

    1. Hi Jim,

      Thanks for you comment. If you mean using the Textbox control you can do it as follows
      1. Click on the Textbox and see the value in the Property Window(normally on the bottom left) to your default text.
      2. Select View->Tab Order and move the Textbox to the top so it will be the selected control.
      3, Add the code below to the UserForm_Initialize() event.

      [sourcecode language=”vb”]
      Private Sub UserForm_Initialize()

      TextBox1.SelStart = 0
      TextBox1.SelLength = Len(TextBox1.Text)

      End Sub

      1. Thanks for the quick answer. That’s works great if it’s always “Banana”, but not exactly what I was looking for. What I’d like is for the textbox value to come from a the value in a cell or a variable. I created a ‘Let’ sub in the userform code window:

        [sourcecode language=”vb”]
        Public Property Let Fruit(whatFruit As Variant)
        textboxFruit.Value = whatFruit
        End Property
        and then added a line to the UserModal sub to set it’s value:

        [sourcecode language=”vb”]
        Dim frm As New UserFormFruit
        frm.Fruit = "Banana"

        When I step through the code, the property gets set in the second line above, but the ‘Let’ code doesn’t fire until after the ‘Initialize’ code has run and the text box string is still empty at that point. I moved the code that highlights the

  5. sorry … hit ‘Send’ accidentally …

    I moved the code that highlights the textbox value to the ‘Let’ sub and it works, but seems a bit inelegant and not really the correct place for it. Maybe you’ll be covering this in the next installment? Thanks again for your help.

    1. Hi Jim,

      You can put the code in the Activate event instead of the Initialize event.

      The difference is that Initialize is used when the actual instance is created. So in your example when the Let event is used.

      The Activate event occurs when the UserForm becomes the active window.

      Private Sub UserForm_Activate()
          textboxFruit.SelStart = 0
          textboxFruit.SelLength = Len(textboxFruit.Text)
      End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *