How to create an Excel VBA UserForm

This article is part of my series: VBA & Macros in Excel – A Step by Step Complete Guide and Make UserForms in VBA Excel with Total 6 Tutorials.

So far we have learned the basics of Excel VBA in our previous chapters. It’s time to work with more advanced topics. In this tutorial, I am going to explain how to create an Excel VBA Userform from scratch.

What is a Userform

In short dialog boxes actually are Userforms. In our various tutorials, we have worked with dialog boxes. If you forgot, no problem at all. Click this link to revise dialog box. Observe the following Insert Function dialog box.

How to create an Excel VBA Userform

Insert Function Dialog Box

To get this dialog box choose Formulas ⇒ Function Library ⇒ Insert Function wizard. There are so many options you can work with in this dialog box like searching for a function, selecting a function and others. At the end, you will select OK or Cancel to quit this dialog box.

Like this dialog box, we can create our own and then it is called Userform.

To follow me with this tutorial, at first download the working files from the download section. Download section is at the end of the post.

Creating the Userform

This is the first Userform that we are going to create. So, I have tried to create the simplest one. This Userform will display a message to the user. Though you can do the same thing using the MsgBox function easily, but a Userform will give you more advantages in terms of manipulating the message.

Read More: How to Create a UserForm: an Overview

Well, lets start creating. Create a new workbook, open it and follow these steps:

    • Choose Developer ➪ Code ➪ Visual Basic (or press Alt+F11). The VB Editor window appears.
    • Double-click on the workbook’s name in the Project window to activate it.
    • Choose Insert ➪ UserForm. The VB Editor opens an empty form named UserForm1 and displays the Toolbox.
    • I’m going to change the name of the Userform and add a caption. You can do these using the properties window. Press F4 to open the Properties window. Properties window has so many options you can manipulate. For now, just change the following:
      PropertyDefaultChange to
      NameUserForm1MessageDisplayBox
      CaptionUserForm1About This Workbook
How to create an Excel VBA Userform

Change Name and Caption of the Userform1

    • We’ll now work with the Toolbox. Toolbox automatically appears when you Insert a UserForm, if it doesn’t appear automatically, choose View ➪ Toolbox. In Toolbox, you will get predefined objects like Label, TextBox, OptionButton etc. Select the Label object from the Toolbox and add it to the UserForm. Label object shows text to users.
How to create an Excel VBA Userform

Toolbox holds the objects.

    • Now we’re going to change the properties of Label object. Select the Label object, right click on it and choose Properties from the Shortcut menu. Properties window will open if it was not. In the Properties window, enter any text in the Label’s Caption property.
    • In the Properties window, double click on the Font property. You will get Font dialog box, where you can adjust several controls (Font, Font Style, Size, Effects, Script) shown in the following figure. A preview will be showed in the Sample box. After you’ve selected all the settings, click OK.
    • In this example, the TextAlign property was set so that the text align in center position. To do this select 2 – fmTextAlignCenter in the TextAlign property.
How to create an Excel VBA Userform

Double click on Font property to display the Font dialog box.

  • I’m going to add another object to the Userform from the Toolbox. It is CommandButton. After adding, use the Properties window to change the following properties for the CommandButton:
PropertyChange to
NameOKButton
CaptionOK
DefaultTrue

The changes are shown in the following figure:

How to create an Excel VBA Userform

Adjusting the properties of the CommandButton object.

 

  • I made other changes so that the form looks good to the user like resizing the Userform, moving and resizing the objects in the Userform.

Finally, our Userform looks like the figure below:

How to create an Excel VBA Userform

So far our Userform looks like this one.

Testing the UserForm

Well, our UserForm has all the necessary controls. Now, it’s time to display the UserForm. While you’re creating the UserForm, press F5 to display the UserForm and see whether it looks like what you want. Click the Close button (X) in the title bar to close the UserForm.

The following process explains how you can write a VBA Sub procedure to display the UserForm when Excel is active:

    • Insert a VBA module by choosing Insert ➪ Module.
    • In the empty module, enter the following code:
Sub ShowMsgDisplayBox()
MessageDisplayBox.Show
End Sub
  • Open your workbook pressing Alt+F11.
  • Choose Developer ➪ Code ➪ Macros or press Alt+F8. The Macros dialog box appears.
  • Select ShowMsgDisplayBox from the list of macros, and then click Run. The UserForm appears.
How to create an Excel VBA Userform

UserForm showed when we run the macro ShowMsgDisplayBox.

Click OK to close the dialog box. Unfortunately, it doesn’t close as you expect. OK button needs to have an event handler. The Event is doing something with the objects (CommandButton, CheckBox, OptionButton etc) in the Userform. In many cases, you clicked OK (under OK, there is a CommandButton) to dismiss the dialog box, clicking OK is an event. We shall write some code for every event, this code is referred to as event handler.

For now, to dismiss the UserForm, click the Close button (X) in its title bar.

Creating an event handler procedure

An event handler is a private type Sub procedure that is executed when we perform the relevant event with the object. In our case, we need a Subprocedure that will handle the Click event (user clicks the OK button to close the dialog box).

    • Press Alt+F11 to activate the VB Editor.
    • Activate the MessageDisplayBox UserForm by double-clicking its name in the Project window.
    • Double-click the CommandButton control (OK button). The VB Editor displays the code module for the UserForm and automatically inserts some boilerplate code in it. See the following figure.
How to create an Excel VBA Userform

This boilerplate code is shown when we double click the CommandButton object in the UserForm.

  • Insert the following statement before the End Sub statement: Unload MessageDisplayBox. The complete event handler procedure is:
    Private Sub OKButton_Click()
        Unload MessageDisplayBox
    End Sub
    

After adding the event procedure, clicking the OK button works as it should.

I have assigned the ShowMsgDisplayBox macro in a button. If you don’t know learn how to assign a macro to a button.

How to create an Excel VBA Userform

I have assigned the macro in a button.

Download Working File

Download the working file from the link below:

display-message-box.xlsm

Happy Excelling ☕

Read More…


Hello! Welcome to my Excel blog! I am conducting deep dives into the world of Excel. Please join with me and explore Excel deeply. Keep in mind this African proverb: "If you want to go fast, go alone, If you want to go far, go together." Let's together explore Excel deeply! ☕

2 Comments
  1. Reply
    Carol November 15, 2016 at 1:37 AM

    I have an application that uses excel userforms. My user is trying out paralells on her Mac to see if we can run the application in the parallels window environment. She just tried to open it.. and says it is excel 2014, but it will not open with the macros. I don’t knlow if it is excel online or excel desktop (I am assuming online)
    Do userforms work in excel 2014? Do they work in excel 2014 online?
    thanks for your help

    Leave a reply