VBA UserForm – A Guide for Everyone

by | Apr 22, 2016 | UserForms | 68 comments

This post provides an in-depth guide to the VBA Userform starting from scratch.

The table of contents below shows the areas of the VBA UserForm that are covered and the section after this provides a quick guide so you can refer back to the UserForm code easily.

“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

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

The Webinar

If you are a member of the website, click on the image below to view the webinar for this post.

(Note: Website members have access to the full webinar archive.)

vba userform1 video

 

Introduction

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.

 

Related Articles

VBA Message Box
VBA UserForm Controls

 

Download the Code

What is the VBA Userform?

The VBA 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.

 

The 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

' https://excelmacromastery.com/
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

' https://excelmacromastery.com/
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"
    Else
        Debug.Print "The user clicked No"
    End If

End Sub

 

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

' https://excelmacromastery.com/
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"
    Else
        Debug.Print "The user clicked Cancel"
    End If

End Sub

You can see all the MsgBox options here.

 

InputBox

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):

' Description: Gets a value from the InputBox
' The result is written to the Immediate Window(Ctrl + G)
' https://excelmacromastery.com/vba-userform/
Sub GetValue()

    Dim sValue As String
    sValue = Application.InputBox("Please enter your name", "Name Entry")
    
    ' Print to the Immediate Window
    Debug.Print sValue

End Sub

 

You can add validation to the InputBox function using the Type parameter:

' https://excelmacromastery.com/
Public Sub InputBoxTypes()

    With Application
        Debug.Print .InputBox("Formula", Type:=0)
        Debug.Print .InputBox("Number", Type:=1)
        Debug.Print .InputBox("Text", Type:=2)
        Debug.Print .InputBox("Boolean", Type:=4)
        Debug.Print .InputBox("Range", Type:=8)
        Debug.Print .InputBox("Error Value", Type:=16)
        Debug.Print .InputBox("Array", Type:=64)
    End With
    
End Sub

You can download the workbook with all the code examples from the top of this post.
 

GetOpenFilename

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

 

The following example allows the user to select multiple files

' https://excelmacromastery.com/
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
    Next
    
End Sub

Note: If you need more flexibility then you can use the File Dialog. This allows you to use the “Save as” file dialog, select folders and so on.

 

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 VBA 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 VBA UserForm Example

Let’s have a look at a very simple UserForm example.

You can download this and all the code examples from the top of this post.

  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 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

 

' https://excelmacromastery.com/
Sub DisplayUserForm()
    
    Dim form As New UserFormTest  
    form.Show
    
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 of the VBA UserForm

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

ControlDescription
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 Code to the VBA UserForm

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 to the VBA UserForm

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 of the VBA UserForm

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

frm.Show

 

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
frm.Show

 

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 VBA UserForm

We can use the VBA 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
frm.Show

As the comments 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 VBA UserForm 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.

You can download this and all the code examples from the top of this post.

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 contents of the fruit textbox:

' PROCEDURE CODE
' https://excelmacromastery.com/
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
    frm.Show
    
    ' Display the returned value
    MsgBox "The user has selected " & frm.Fruit
        
    ' Close the form
    Unload frm
    Set frm = Nothing

End Sub

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

' Hide the UserForm when the user click Ok
Private Sub buttonOk_Click()
    Hide
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.

 

Using UserForm_QueryClose to Cancel the UserForm

We always want to give the user the option to cancel the UserForm. Once it is canceled 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

 

' https://excelmacromastery.com/
Sub DisplayFruit()
    
    Dim frm As New UserFormFruit
    frm.Show
        
    ' 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 from being Unloaded when the X button is clicked. To do this we use the QueryClose event.

We create a variable first at the top of the UserForm code module. We also add a property so that we can read the variable when we need to retrieve the value:

Private m_Cancelled As Boolean

Public Property Get Cancelled() As Variant
    Cancelled = m_Cancelled
End Property

 

Then we add the UserForm_QueryClose event to the UserForm module:

' https://excelmacromastery.com/
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
    Hide
    m_Cancelled = True
    
End Sub

 

In the first line, we prevent the UserForm from 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 canceled:

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

' PROCEDURE CODE
' https://excelmacromastery.com/
Sub DisplayFruit()
    
    Dim frm As New UserFormFruit
    frm.Show
        
    If frm.Cancelled = False Then
        MsgBox "You entered: " & frm.Fruit
    Else
        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:

' https://excelmacromastery.com/vba-userform/
Private Sub buttonCancel_Click()
    ' Hide the Userform and set cancelled to true
    Hide
    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:

' USERFORM CODE
' https://excelmacromastery.com/
Private m_Cancelled As Boolean

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

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

' Hide the UserForm when the user click Ok
Private Sub buttonOk_Click()
    Hide
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
    Hide
    m_Cancelled = True
    
End Sub

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

&nbps;
You can use this code as a framework for any Modal UserForm that you create.

 

VBA Minimize UserForm Error

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

You can download this and all the code examples from the top of this post.

VBA Modeless Userform example

 

The code below displays the UserForm in Modeless mode. The problem with this code is that if you minimize Excel the UserForm may not be visible when you restore it:

' PROCEDURE CODE
' https://excelmacromastery.com/
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

 

The code below solves the problem above. When you display a Userform using this code it will remain visible when you minimize and restore Excel:

' https://excelmacromastery.com/
Sub UseModelessCorrect()

    Dim frm As Object
    Set frm = VBA.UserForms.Add("UserFormCustomer")
    
    frm.Show vbModeless
    
End Sub

 

An important thing to keep in mind 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.

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 on the Close button.

 

The following is the UserForm code for the customer example:

' USERFORM CODE
' https://excelmacromastery.com/
Private Sub buttonAdd_Click()
    InsertRow
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
        Else
            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?

Free VBA Tutorial If you are new to VBA or you want to sharpen your existing VBA skills then why not try out the The Ultimate VBA Tutorial.

Related Training: Get full access to the Excel VBA training webinars and all the tutorials.

(NOTE: Planning to build or manage a VBA Application? Learn how to build 10 Excel VBA applications from scratch.)

 

68 Comments

Newer Comments →
  1. Henry
    Henry on April 23, 2016 at 10:28 am

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

    Reply
    • Paul Kelly
      Paul Kelly on April 23, 2016 at 6:24 pm

      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
      Reply
  2. Michael
    Michael on April 26, 2016 at 7:48 pm

    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!

    Reply
    • Paul Kelly
      Paul Kelly on April 27, 2016 at 11:05 am

      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.

      Regards,
      Paul

      Reply
  3. Ludo
    Ludo on April 27, 2016 at 7:06 pm

    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,
    Ludo

    Reply
    • Paul Kelly
      Paul Kelly on April 28, 2016 at 9:49 am

      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.

      Reply
  4. Jim
    Jim on May 2, 2016 at 9:25 pm

    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?

    Reply
    • Paul Kelly
      Paul Kelly on May 3, 2016 at 10:06 am

      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
      [/sourcecode]

      Reply
      • Jim
        Jim on May 3, 2016 at 2:07 pm

        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
        [/sourcecode]
        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"
        frm.Show
        [/sourcecode]

        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

        Reply
  5. Jim
    Jim on May 3, 2016 at 2:10 pm

    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.

    Reply
    • Paul Kelly
      Paul Kelly on May 3, 2016 at 3:38 pm

      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
      Reply
  6. Jim
    Jim on May 3, 2016 at 8:43 pm

    Awsome! Thanks Paul.

    Reply
  7. Santosh Mangang
    Santosh Mangang on February 1, 2017 at 11:14 am

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

    Dim form As New UserFormTest
    form.Show

    End Sub
    [/sourcecode]

    On pasting the above code, and running the macro.
    A compile error “Invalid Outside Procedure” pops up.
    I’m new to vba can you please help?

    Reply
    • Paul Kelly
      Paul Kelly on February 1, 2017 at 11:38 am

      Hi Santosh,

      The first and last lines are for WordPress formatting and not part of VBA. I’ve updated the code in the post – it should look like this

      Sub DisplayUserForm()
    
    Dim form As New UserFormTest  
    form.Show
    
End Sub
      Reply
  8. Linus
    Linus on February 22, 2017 at 11:43 am

    I want user not to use pc when userform is in full screen open conditions. I tried but alt tab is working. Any ideas

    Reply
  9. Ray Lagasse
    Ray Lagasse on March 5, 2017 at 9:22 pm

    Paul, how do I return the cursor to the first entry box after I hit the “enter data” command button?

    Reply
    • Paul Kelly
      Paul Kelly on March 7, 2017 at 7:26 am

      TextBox1.SetFocus

      Reply
  10. Gina
    Gina on June 26, 2017 at 4:31 pm

    Paul,

    How do I open a Custom Excel Form that I designed to a specific record.

    Thanks!

    Reply
    • Paul Kelly
      Paul Kelly on June 27, 2017 at 8:42 am

      Are you referring to Access VBA or Excel VBA?

      If Excel, then what control are you using to display the records?

      Reply
  11. Davor
    Davor on September 5, 2017 at 10:10 am

    Paul,
    very, very interesting and detailed article.

    Davor

    Reply
    • Paul Kelly
      Paul Kelly on April 11, 2018 at 2:24 pm

      Thanks Davor.

      Reply
  12. pavithran
    pavithran on December 24, 2017 at 2:16 pm

    Below vba update same cell. but only 3 rows can update .i have 16 rows ca you fix.

    Private Sub prSave_Click()
    If Me.ComboBox1.Value = “” Then
    MsgBox “Name Can Not be Blank!!!”, vbExclamation, “Name”
    Exit Sub
    End If
    SLNo = Me.ComboBox1.Value
    Sheets(“Data”).Select
    Dim rowSelect As Double

    Dim msg As String

    rowSelect = Me.ActiveControl
    rowSelect = rowSelect + 7
    Cells(rowSelect, 2) = Me.TextBox1.Value
    Cells(rowSelect, 3) = Me.TextBox2.Value
    Cells(rowSelect, 4) = Me.TextBox3.Value
    Cells(rowSelect, 5) = Me.TextBox4.Value
    Cells(rowSelect, 6) = Me.TextBox5.Value
    Cells(rowSelect, 7) = Me.TextBox6.Value
    Cells(rowSelect, 8) = Me.TextBox7.Value
    Cells(rowSelect, 9) = Me.TextBox8.Value
    Cells(rowSelect, 10) = Me.TextBox9.Value
    Cells(rowSelect, 11) = Me.TextBox10.Value
    Cells(rowSelect, 12) = Me.TextBox11.Value
    Cells(rowSelect, 13) = Me.TextBox12.Value
    Cells(rowSelect, 14) = Me.TextBox13.Value
    rowSelect = rowSelect – 7
    Cells(Application.ActiveCell.Row, 7).Select
    msg = “You selected Cell Succesfully updated if you want continue… ?”
    Unload Me
    ans = MsgBox(msg, vbYesNo, “PAVI-TR-EQUIPMENT INSPECTOR”)
    If ans = vbYes Then
    UserForm2.Show
    Else
    Sheets(“Data”).Select
    End If
    End Sub

    Reply
  13. Gaurav T
    Gaurav T on February 2, 2018 at 10:12 pm

    Hello Paul,

    This is great stuff. Honestly, can’t thank Google enough for having me stumble upon your website. I am recently starting to learn VBA in order to create a supplier form for our company. We have several suppliers and many invoices in a given month, so I decided to come up with an easier way to register invoices linked to the respective supplier and then connect this with the various methods of payments we have. I have already sketched out how I would like this to work out and the UserForms. I am really good with excel formulas and can handle it really well, but when it comes to VBA, I have no knowledge of the codes. As a result, the current format I have on my Excel is something only I can use, as others are not able to understand the complexity of my Excel system. Thus, i am creating one using VBA for everyone to use and not just me – making things much simpler.

    My main question here is if there is a way to automatically fill a set of TextBoxes on the UserForms. For instance, after having registered the supplier and its details regarding bank details as step one, I want the user to be able to select the supplier whos invoice he/she is entering in the userform, and automatically fill in the other details of the textboxes, such as bank details – then allowing them the option of agreeing with the bank details and if the user wants to change the details given a change on the invoice.

    I hope I am clear enough on what I am looking for, and if you could please help me get started with this new project I am working on.

    Reply
    • Paul Kelly
      Paul Kelly on April 11, 2018 at 2:18 pm

      Hi Gaurav,

      You can put the suppliers in a ComboBox and allow the user to select from this box. Then you can use the Change event of the combobox to file the textboxes based on the current supplier.

      Reply
  14. Alanv
    Alanv on February 22, 2018 at 11:25 pm

    Awesome, Paul. The sharing is very informative. Even as I am a newbie, this given me a lot of information.

    Reply
    • Paul Kelly
      Paul Kelly on February 25, 2018 at 10:14 am

      You’re welcome. Glad you found it useful.

      Reply
  15. Jordi
    Jordi on February 27, 2018 at 11:25 am

    Paul,

    Thanks for this guide. It’s very detailed and well explained.
    I have 200 buttons and I want to use the click events in one module. I have seen that I have to change the event to Public and use:
    Call userform1.commandbutton1_Click
    or
    userform1.commandbutton1_Click
    I would like to change the number of the button with a variable, but I don’t know how to do it.
    I was able to do it of I want to change the button properties with:
    userform1.Controls(“commandbutton” & i).caption = “test”
    Many thanks!

    Reply
    • Paul Kelly
      Paul Kelly on April 11, 2018 at 2:24 pm

      Hi Jordi,

      I’m not clear on what you are trying to do. Can you explain what you mean by “change the number of the button with a variable”.

      Paul

      Reply
  16. Jhon Camacho
    Jhon Camacho on June 1, 2018 at 4:05 pm

    Incredibly useful!! Thanks!

    Reply
    • Paul Kelly
      Paul Kelly on June 2, 2018 at 9:33 am

      You’re welcome

      Reply
  17. Jeff Johnson
    Jeff Johnson on June 25, 2018 at 11:42 am

    Paul,

    Is there a particular reason you use variant data type for the form properties rather than a boolean or string?

    Thank you

    Reply
    • Paul Kelly
      Paul Kelly on June 25, 2018 at 12:47 pm

      Hi Jeff,

      They should be boolean and String. I have updated the post.

      Thanks
      Paul

      Reply
  18. Shane
    Shane on October 6, 2018 at 1:07 am

    Paul, thanks so much for the information. I want to call other sub procedures from within the UserForm on startup, but I dont know whether that should be in the Initialize or Activate procedure. In either case, when I step through the code with F8, it does not update the date label or step to the subprocedures to prefill the text boxes with user goals that they may have entered for current day on a prior day.
    Private Sub UserFormStartDay_Initialize()
    ‘ set up the Daily Goals User Form
    Dim myDate As Date
    myDate = VBA.DateTime.Date ‘ set myDate to today
    ‘ start with today
    Me.LabelSD_TodayDate.Caption = myDate ‘ – the label caption does not change to current date
    Me.LabelSD_TodayDate.Caption = Format(Date, “MM/DD/YYYY”)
    ‘ run the AutoPopulateStartDay to pull in Summary Data
    Call AutoPopulateStartDay ‘ put in textbox values for today from another sub procedure – cannot get this to run, regardless of whether it is a Private or Public procedure

    Reply
    • Paul Kelly
      Paul Kelly on October 15, 2018 at 5:19 am

      Hi Shane,

      Initialize is when the UserForm is first created.

      Activate is used everytime the Userform is shown. So if you hide the userform and then show it again this will run.

      Put a breakpoint(F9) on the first line of the sub and see if it stops here.

      Paul

      Reply
  19. Shawn
    Shawn on October 29, 2018 at 8:32 pm

    Your VBA posts are great! I really appreciate the time you spend doing this

    Reply
    • Paul Kelly
      Paul Kelly on October 30, 2018 at 12:38 pm

      Thanks Shawn

      Reply
  20. Jan
    Jan on December 10, 2018 at 5:31 am

    Hi Paul,

    I’m new to userforms and your article is a great starting point. Like all your posts. Thank you so much.

    Reply
    • Paul Kelly
      Paul Kelly on August 27, 2019 at 6:55 am

      Hi Jan,
      Glad you like this post.
      Thanks.

      Reply
  21. Tim Eckersley
    Tim Eckersley on February 6, 2019 at 5:28 pm

    Hi Paul, I am using Excel VBA. Is there a way to make the caption font in a UserForm bold and (say) size 14?
    I have looked around, but cannot find an obvious solution. (By the way, the website is great)!
    Best regards, Tim

    Reply
    • Paul Kelly
      Paul Kelly on February 8, 2019 at 3:11 am

      Hi Tim,

      There is no simple way to do it. You need to use a Windows API.
      This is a good discussion about it.

      Paul

      Reply
  22. Tim Eckersley
    Tim Eckersley on February 15, 2019 at 8:41 pm

    Thank you Paul. Not simple at all. That code is quite beyond me. I tried using it, but ran into an error immediately. I may take the easier approach of using a faux caption as described in the first part of that article. That – I can handle! Thank you again for the help. Tim

    Reply
  23. Quinton
    Quinton on February 20, 2019 at 9:55 pm

    Hi Paul:

    Very useful explanation of some of the details of a userform. I have a question about control of a userform that might be a combination of modal and modeless.

    In an automated process, a (separate) main userform takes information and automatically opens, finds, populates and prints a worksheet. That now works well, incorporating some of your advice. Adding the ability to loop through the process repeatedly is now something I’m adding. The complication is that there are times when manual modification of entered information before printing is desirable.

    After all the user-selections /-limited-modification-info are entered in a main userform, a second, simple userform is shown to control re-starting the loop. When the populating-and-printing process is performed automatically, the user chooses to start the loop again with the simple userform, so showing the userform modal works correctly — the code waits until a choice is made. However, with manual modification (an option selected by the user in the main userform, so it’s known before the worksheet is opened), I would like the same simple userform to be shown, for the code to wait until a selection is made AND still allow the user to interact with the worksheet.

    Modal would cause the code to wait, however the user can’t interact with the worksheet. Modeless won’t pause the code but does allow interaction. Is there a method to combine the behaviour of the two?

    ‘Thanks. Quinton.

    Reply
    • J. Woolley
      J. Woolley on August 21, 2019 at 4:34 pm

      You can show your form modeless, then use a tight loop to pause your module’s code until you’re ready to resume:

      Dim dSec As Date, form As MyModeless
      dSec = TimeSerial(0, 0, 1)
      Set form = New MyModeless
      Load form
      form.Show vbModeless
      Do Until form.Tag = “DONE”
      Application.Wait (Now + dSec)
      DoEvents
      Loop
      Unload form
      Set form = Nothing

      In your form’s code (for example, a Button_Click event), set
      Me.Tag = “DONE”
      to indicate it’s time to resume your module’s code.

      This is not pretty, but it works.

      Reply
      • Paul Kelly
        Paul Kelly on August 22, 2019 at 6:49 am

        Thanks J

        Reply
    • J. Woolley
      J. Woolley on August 22, 2019 at 9:29 pm

      Here’s another way for a hybrid-mode form. It involves two procedures. Proc_1 will show your form modeless and tell it the name of Proc_2, then exit. Proc_2 will start when you are finished with the modeless form.

      Sub Proc_1()
      Dim form As MyModeless
      Set form = New MyModeless
      Load form
      form.ProcName = “Proc_2”
      form.Show vbModeless
      Set form = Nothing ‘but don’t Unload
      End Sub

      Sub Proc_2()
      ‘do whatever
      End Sub

      At the beginning of your form’s code:

      Private sProcName As String

      Public Property Let ProcName(Text As String)
      sProcName = Text
      End Property

      Private Sub UserForm_Initialize()
      sProcName = vbNullString
      End Sub

      In your form’s code (for example, a Button_Click event):

      If Len(sProcName) > 0 Then Application.OnTime Now, sProcName
      Unload Me

      I think this is better than my previous method.

      Reply
  24. Arun Kumar M
    Arun Kumar M on July 15, 2019 at 6:58 am

    I am willing to learn USER FORMS. Kindly guide me through and if there is any documents request to send for the below mail ID.

    Reply
  25. Sam
    Sam on July 29, 2019 at 10:13 pm

    I’d created a presentation/tutorial in MS PowerPoint 2013, run it in a terminal mode, where users navigate through vba coded buttons. Now, I’m figuring a way to capture who views the PowerPoint (also, trying to capture their feedback). Could please help with a vba code that could be tied to a userform in the beginning or the end of the presentation that would allow users to fill and submit the feedback/registrations?

    Could inserting an embedded Excel help with this?

    Reply
  26. J. Woolley
    J. Woolley on August 23, 2019 at 4:16 pm

    Paul:

    Under “Putting All the Modal Code Together” you have:

    ‘ USERFORM CODE
    Private m_Cancelled As Boolean

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

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

    Private Sub buttonCancel_Click()
    m_Cancelled = True
    End Sub

    ‘ PROCEDURE CODE
    ‘ Check if the user cancelled the UserForm
    If frm.Cancelled = True Then
    MsgBox “The UserForm was cancelled.”
    Else
    MsgBox “You entered: ” & frm.Fruit
    End If

    It seems simpler to change this as follows:

    ‘ USERFORM CODE
    Public Cancelled As Boolean

    ‘ Delete code for Public Property Get Cancelled

    Private Sub buttonCancel_Click()
    Cancelled = True
    End Sub

    Your PROCEDURE CODE does not need to be changed.

    Defining Fruit as a UserForm Property allows the PROCEDURE CODE to reference it as frm.Fruit instead of frm.textboxFruit.Value. This is a (small) benefit because it hides the TextBox’s name (which might be changed).

    But why bother to define a UserForm’s global variable Private with a Public Property to Get or Let it? Isn’t it easier to make the global variable Public, which automatically makes it a Property?

    Reply
    • Paul Kelly
      Paul Kelly on August 25, 2019 at 7:49 am

      In Object-Oriented code we want to hide the implementation details from the user. We use the Get/Let/Set properties to create layer between them. The user shouldn’t have access to the variables of a class object\userform.

      The main reason is that if we change the underlying code in our object\userform then all the code that calls it breaks. But if the user can only access properties then the calling code is not affected as long as the property name and return value is the same.

      Reply
  27. J. Woolley
    J. Woolley on August 26, 2019 at 9:03 pm

    Paul:

    The UseModeless macro (under “How to Use a Modeless form”) will show the form as described. But if Excel is minimized, the form loses state and will be gone when Excel is restored. How do you prevent this loss of state and keep the form from disappearing?

    Reply
    • J. Woolley
      J. Woolley on August 30, 2019 at 7:54 pm

      This excerpt from “How to Use a Modeless form” will show the form, but it will not be persistent. If Excel is minimized, the form loses state and will be gone when Excel is restored.

      Dim frm As New UserFormCustomer
      frm.Show vbModeless

      There are two ways to make the form persistent so it will not lose state and disappear after minimize-restore.

      (1) Every UserForm class has a default instance; use the default instance of UserFormCustomer directly:

      UserFormCustomer.Show vbModeless

      But there is never more than one default instance of UserFormCustomer; if the previous statement is executed again, the new instance will replace any current instance.

      (2) The preferred method for creating a new instance of UserFormCustomer is VBA.UserForms.Add:

      Dim frm As UserFormCustomer ‘ New keyword is optional (and has no effect)
      Set frm = VBA.UserForms.Add(“UserFormCustomer”)
      frm.Show vbModeless

      A separate persistent instance will be created each time these statements are executed; each will be in the same screen position unless manually offset. In this case, Add triggers the UserForm_Initialize event (which was triggered by Show previously). In each case, Load before Show is optional; however, Load after Add is superfluous.

      Reply
      • Paul Kelly
        Paul Kelly on August 31, 2019 at 9:02 am

        Thanks. I’ll have a look into the points you made and update the post if necessary.

        Reply
  28. Todor Todorov
    Todor Todorov on September 24, 2019 at 6:29 am

    you publish very good material (my english is very weak)

    Reply
  29. Jan van Lith
    Jan van Lith on January 13, 2020 at 12:09 pm

    Hi Paul,

    I hop you can help me with the following issue.
    I have built a spreadsheet with two different (related) databases, which can be controled by two different userforms (I have made separate buttons for both userforms so that the user can open the userform that he needs). Each userform consists of multipages with several frames and a lot of textboxes and comboboxes.

    The code for these two userforms is very much alike: it is code to enter new records, to change a record, to update the database, etc. But as I have two different userforms, the code in fact is written twice. For reasons of efficiency and maintenance, this seems not to be the optimal solution.

    So my question is: is it possible to (partly) write the code only once and save it on a location that can be accessed by both userforms? If not –> bad luck. If it is possible –> how do I have to structure this.

    I hope my question is clear and I am looking forward to your ideas on this issue.

    Thanks,

    Jan

    Reply
  30. Maria
    Maria on February 8, 2020 at 4:16 pm

    Dear Paul,
    Really well explained. Now, I’m trying to set a UserForm that shall start to insert data at cell A46. How can I modify your “InsertRow” function to do it? Thank you

    Maria

    Reply
Newer Comments →

Submit a Comment

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