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.
Table of Contents
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.
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:
Property Default Change to Name UserForm1 MessageDisplayBox Caption UserForm1 About This Workbook
- 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.
- 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.
- 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:
The changes are shown in the following figure:
- 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:
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.
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.
- 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.
Download Working File
Download the working file from the link below:
Happy Excelling ☕