VBA UserForm – A Guide for Everyone

VBA Userform

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



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


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

You can see all the MsgBox options here.


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 = Application.InputBox("Please enter your name", "Name Entry")
    Debug.Print sValue

End Sub

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

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



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

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


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 String
    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 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
    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 Add Customer button.

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 is not visible when you restore it:

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:

Sub UseModelessCorrect()

    Dim frm As UserFormCustomer
    Set frm = VBA.UserForms.Add(UserFormCustomer.Name)
    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 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?

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


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
  6. [sourcecode languague=”vb”]
    Sub DisplayUserForm()

    Dim form As New UserFormTest

    End Sub

    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?

    1. 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  
      End Sub
  7. Paul, how do I return the cursor to the first entry box after I hit the “enter data” command button?

    1. Are you referring to Access VBA or Excel VBA?

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

  8. 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
    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
    End If
    End Sub

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

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

  10. 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
    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!

    1. 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”.


  11. Paul,

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

    Thank you

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

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


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

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

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

    1. 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)
      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.

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

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

  17. 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?

  18. Paul:

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

    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

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

    It seems simpler to change this as follows:

    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?

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

  19. 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?

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

Leave a Reply

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