Microsoft Excel Macros                              

 

Excel VBA Macros

Excel Macros Table of Contents

Excel Visual Basic Editor

Excel Macros Vocabulary

Excel Userforms

Excel Consulting

Excel Tutorial on Macros

Excel Macros Userform Exercises

Print this page, open Excel and complete the exercises below

If you have any difficulties with these exercises write to:
[email protected]

Creating a Userform in Excel

Now that you know how to work in the Visual Basic Editor (lessons 1 to 4) here is a series of 8 simple and easy exercises to help you create and use your first userform. The form that you will create and program will allow a user to enter a value in a cell.

Userforms are created in the Project Window of the Visual Basic Editor. You will also find the toolbox that allows you to add controls to your userforms in the Visual Basic Editor.

First, make sure that the level of security of your copy of Excel allows you to use macros.

For users of Excel 1997 to 2006: The first thing that you need to do is to make sure that the security level of Excel is set at either "Low" or "Medium" so that you can use the macros (VBA procedures) that you develop. From the menu bar of Excel select "Tools" then "Macro" then "Security" and select "Medium".

For users of Excel 2007 to 2010: From the "Developer" ribbon click on the "Macro Security" button. Check the second level "Disable all Macros with Notification" and you are set.

Exercise 1: Create your first userform

Step 1: Open Excel and a new workbook.

Step 2 (Excel 1997 to 2006): Go to the menu bar and select "File/SaveAs". Select the desktop in the "Save in:" drop-down list and in the "File name:" text box enter formTest.xls. Click "OK".

Step 2 (Excel 2007 to 2010): Go to the Ofiice button   and select "SaveAs". Select "Excel Macro-Enabled Workbook". Select the desktop in the "Save in:" drop-down list and in the "File name:" text box enter formTest.xlsm. Click "OK".

The next steps are the same whatever version of Excel you are working with. The only difference is that the file name in the project window will be either formTest.xls or formTest.xlsm

Step 3: Use the "ALT/F11" keys to move to the Visual Basic Editor

Step 4: Right click anywhere in the project window. You will see this menu appear:

Step 5: Go to "Insert" and select "UserForm". You will then see the following:

On the right you see the userform that you have just added to your workbook. On the left is the toolbox with all the controls that you can add to your userform. You can hide that toolbox by clicking on the "X" and bring it back by going to the menu bar "View/Toolbox". We will use the toolbox later in this section.

Notice that UserForm1 is selected in the project window so you see the properties of the userform in the properties window.

Step 6: In the project window, double click on Sheet1 and the userform disappear.

Step 7: Double click on UserForm1 and the form comes back.

Step 8: Use the ALT/F11 keys to go back to Excel


Exercise 2: Userforms Properties

In this segment of the exercise we will change the name of the userform and the header in the blue section at the top of the form.

Step 1: Use the ALT/F11 key to go to the Visual Basic Editor

Step 2: If the UserForm1 and the toolbox are not visible, double click on the name UserForm1 in the project window and recall the toolbox as you have learned in the preceding exercise. Here is what you will see:

Step 3: We will change 2 properties of the userform. You will notice that I always use a 3 letter prefix (lower case letters) when I create names for userforms and controls. For userforms the prefix is frm. In the properties window the value of the (Name) property is frmDataEntry. Double click on UserForm1, type in frmDataEntry and click "Enter". The value of the Caption property is also UserForm1. Again double click that UserForm1, type in Data Entry  and click "Enter". The result is shown in the image below. The header of the form is now Data Entry and its programmatical name is frmDataEntry.

Step 4: Save the workbook.


Exercise 3: Adding a Label to a Userform

There are 15 icons in the toolbox (the black arrow is NOT a control). If you move the cursor over each of the icons you will discover the name of the control that it represents. As you can see in the images below when the cursor is over the large "A", the word Label appears in a small text box. If the second icon is selected the words Text Box appear and if the third icon is selected the words Combo Box appear. You will learn about all 15 controls in lessons 26 to 33 of the Tutorial on Excel Macros

         

To add a label to a userform you left click on its icon in the toolbox. You move the cursor to the userform, you click again and the label is added. You can then resize it to your liking.  If you double click on the label icon in the toolbox you can then click on the form as many times as you need labels. When you are finished adding labels just click once on the label icon of the toolbox.

Step 1: Use the ALT/F11 key to move to the Visual Basic Editor.

Step 2: If the userform is not visible, double click on its name in the project window. If the toolbox is not visible refer to the exercise above to make it visible. You will then see this:

Step 3: Left click on the icon of the label in the toolbox (A) and then click on the userform itself and the label will appear. At this stage you will notice that the properties window shows the properties of the label as in the image below:

Step 4: We will change two properties of the selected label. For labels the prefix is lbl. So go to the properties window and change the (Name) property of the label to lblFirstName and the Caption to Enter a Value. You will see this on your screen:

Step 5: As you notice on the userform there is a border and resizing handles around the label when it is selected and the properties window shows the properties of the label. In this state you can move the control around by just clicking on it, holding the button and dragging the control to wherever you want it to be. Click anywhere on the form and you will see a border and resizing handles around the userform itself and the properties window showing the properties of the userform. Click again on the label and.....

Step 6: Use the ALT/F11 keys to go back to Excel

Step 7: Save the workbook


Exercise 4: Adding a Text Box to a Userform

To add a text box to a userform you left click on its icon in the toolbox. You move the cursor to the userform, you click again and the text box appears. You can then resize it to your liking. 

Step 1: Use the ALT/F11 key to move to the Visual Basic Editor.

Step 2: If the userform is not visible, double click on its name in the project window. If the toolbox is not visible refer to the exercise above to make it visible. You will then see this:

Step 3: Left click on the icon of the text boxes in the toolbox and then click on the userform itself and the text box will appear. At this stage you will notice that the properties window shows the properties of the text box as in the image below:

Step 4: We will change only one property of the selected text box. For text boxes the prefix is tbx. Go to the properties window and change the (Name) property to tbxFirstName. You will se this on your screen:

Step 5: As you notice there is a border and resizing handles around the text box and the properties window shows the properties of the text box. Click anywhere on the form and you will see a border and resizing handles around the userform and the properties window shows the properties of the userform. Click on the label and you will see a border and resizing handles around the label. Click again on the text box and.....

Step 6: Use the ALT/F11 keys to go back to Excel

Step 7: Save the workbook


Exercise 5: Adding a Command Button to a Userform

To add a command button to a userform you left click on its icon in the toolbox. You move the cursor to the userform, you click again and the command button appears. You can then resize it to your liking.

Step 1: Use the ALT/F11 key to move to the Visual Basic Editor.

Step 2: If the userform is not visible, double click on its name in the project window. If the toolbox is not visible refer to the exercise above to make it visible. You will then see this:

Step 3: Left click on the icon of the command button in the toolbox and then click on the userform itselfand the command button will appear. At this stage you will notice that the properties window shows the properties of the command button as in the image below:

Step 4:We will change two properties of the selected label. For command buttons the prefix is cmd. Go to the properties window and change the (Name) property to cmdSubmit and the Caption (what is shown on the command button) to Submit. You will se this on your screen:

Step 5: As you notice there is a border and resizing handles around the command button and the properties window shows the properties of the command button. Click anywhere on the form and you will see a border and resizing handles around the userform and the properties window shows the properties of the userform. Click on the label , the text box and the command button and you will see a border and resizing handles around them and the properties window will show their properties. IN the exercise below you will double click on the command button to see its VBA code.

Step 6: Click on the userform and use the resizing handles to reduce the size of the userform. Your userform should look like the one in the image below.

Step 7: Use the ALT/F11 keys to go back to Excel

Step 8: Save the workbook.


Programming the Userform

When you develop simple userforms most of the programming is created within the command button of the form. Here is an exercise to create VBA code within the command button of your new userform and to test it.


Exercise 6: VBA Coding

You will develop VBA code so that when the user clicks on the Submit button of the userform the value from the text box of the form will be entered in cell C1 of the sheet. Any new value submitted with the form will replace the previous value entered in cell C1.

Step 1: Use the ALT/F11 keys to go to the Visual Basic Editor. At the end of the previous exercise, the screen showed you this:

Step 2: Double click on the "Submit" command button. The form will disappear from the code window and two lines of a macro will appear in the like in the image below (if you do not see the Option Explicit in your code window do not worry for now.

After you have completed this exercise, whatever is between the Sub and End Sub lines will be executed when a user clicks on the command button that you have named cmdSubmit

Step 3: In between the Sub and End Sub lines enter the three following VBA sentences:
Range("C1").Value=tbxFirstName
tbxFirstName=Empty
frmDataEntry.Hide

Key them in or copy/paste them from this website. Your code window now looks like the image below.

In plain English these three sentences say: Enter in cell C1 the value that has been entered by the user in the text box named tbxFirstName. Remove the value that is in the text box named tbxFirstName so that when the form is called again the text box will be empty. Then hide the userform named frmDataEntry.

Congratulations! You have now developed your first userform and it is fully operational.

Step 4: Use the ALT/F11 key to go back to Excel

Step 5: Save the workbook.


Exercise 7: Testing your Userform

In a more elaborate exercise in the downloadable tutorial on excel macros you will learn how to add command buttons to the sheet to call the userforms. For the time being here is how you can call and test your userform.

You will create a small macro to call the userform so that you can use it.

Step 1: Use the ALT/F11 keys to go to the Visual Basic Editor.

Step 2: Double click on Sheet1

Step 3: In the code window type in or copy/paste the following macro:
Sub proShowForm()
      frmDataEntry.Show
End Sub

Step 4: Use the ALT/F11 keys to go to Excel.

Step 5: Save the workbook

Step 6 (Excel 1997 to 2006): Go to "Tools/Macro/Macros", select "proShowForm" and click "Run". The userform appears.

Step 7 (Excel 2007 to 2010): From the Developer ribbon click on Macros , select "proShowForm" and click "Run". The userform appears.

Step 7: Enter a first name in the text box and click on the command button Submit. See that the value of the text box is transferred to cell C1 of the sheet Database and the userform disappears.

Step 8: Repeat steps 6 and 7 as many times as you want. You will see that cell C1 always contain the last value that has been submitted through the form


Congratulations you are no longer a beginner!
You are now ready to learn much more

Click below to Purchase and Download Immediately
your own copy of the
Tutorial on Excel Macros

 

 

Excel Tutorial on Macros

Excel VBA Macros

Excel Macros Table of Contents

Excel Visual Basic Editor

Excel Macros Vocabulary

Excel Userforms

Excel Consulting