VBA Tutorial – Create a Userform in Excel 2013

Excel is the leader in business spreadsheets for a reason. VBA or Visual Basic for Applications, allows developers to develop a number of programs and applications designed specifically for an organization. This tutorial will show you how to create a simple user form for your worksheet. This form will allow users to add sales to the worksheet. This tutorial will assume you are familiar with the basics of Excel. You must be able to open a worksheet, enter data, navigate the ribbon and know where to find the developer tab. If you need to improve your Excel skills, enroll in the Learn Microsoft Excel 2013 course. This course offers over 102 lectures and 3.5 hours of content designed to teach you the basics of Excel. You will learn the basics of worksheets and then learn about formatting. The course will teach you how to work with formulas, illustrations and charts. You will learn how to print and share your workbooks as well as learning some of the advanced features of Excel. By the end of the course you will be able to prove your mastery of Excel and will be capable of using Excel in a variety of different settings.

To create a user form you need to:

1.       Add a form to a worksheet

2.       Create Labels and Elements on the Form

3.       Add VBA Code to enter the data onto the Form

4.       Add a Button to open the Form

Add a Form

We are going to use the VBA editor to add a custom user form to our worksheet. To add a form, open the VBA editor and then select “Insert” and select “User Form”. A new user form will be added to your VBA Editor:

You can use the handles around the form to resize the form to a size you want. The options on the left include a toolbox to allow us to add elements called “controls” to the form. Most forms require at least one element. Using the properties window, we will change the caption of the form to “Enter Sales.” Under the properties, select Name and change the caption to “User Sales.”

We will now use the form toolbox to add two combo boxes and a textbox to our form. Combo boxes are boxes that contain information that the user can select. They are often also referred to as drop down lists although dropdown lists can refer to something different in Excel. The two combo boxes will collect data from our worksheet for the Items and Prices. The text box will allow users to enter quantity sold.

Our worksheet already contains a list of items and prices. You need to create two named ranges, one that contains the items and one that contains the prices. Call your named lists “Items” and “Prices”.

To add the first combo box, click the combo box tool on the toolbox and then drag the area onto your form.

Now change the name of the combo box to “ItemSold”. Under row source type in “Items” this will use your named range as the selection for the combo box.

Add a label for the Combo box called “Items”. Add a label and then change the caption of the label to “Items”.

Now add a combo box for the prices. Call the combo box “ItemPrice” and type in the row source as Prices – the named range in your worksheet:

Now we will add a final text box for the quantity sold. Change the name to “QuantitySold.”

Now we need to add a button to save the record once the user has entered the value. Add a command button and call it “Add Sale”:

Now we have all the elements we need for our user form so we need to add the code to add the data the user enters into our form.

Add VBA Code to Enter the Data Onto the Form

Our code will take the values the user enters into the form and add a new row to our worksheet and save the data to the new row. In order to do this, we first need to ask Excel to find the last row of data so that we can enter the data into the next available row. To do that we will use this VBA code:

eRow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

This code creates a variable called eRow. We then ask Excel to find the last row on our worksheet that contains data and then to add one to that row to work out what the first new row will be. Now we will use the row number when we enter the values. To enter the values we will assign the values to a specific cell address. We will use the following text to assign the values:

Cells(eRow, 1).Value = ItemSold.Text

What the above text is doing is saying for the cell with the address of eRow (the next new row), in the first column, add the value of ItemSold. ItemSold is the name of the combo box we created, so the value will be what the user selected for ItemSold. We will add similar code to add the other values.

Your completed code for the button will look like this:

Now we need to add a button to start the form.

Add a Button to Open the Form

We need to add a button that the user can click to open the form. You can also include this in the main routine so that the form automatically opens when the worksheet is opened. For the purposes of this tutorial, we will create a button that opens the form for us.

Add a command button to the sheet that is called “Add Sales” by choosing Insert “Command Button” from the ActiveX controls. Double click the button to add the VBA code:

We called our form UserForm at the start of this tutorial. Now all we need to do is add the .Show command and the form will open when the user clicks the button:

When the user clicks the button, the form opens:

And when the user clicks “Add Sale” a new line is added to the worksheet automatically because of the VBA code we wrote.

Add Forms to Your Worksheets Today

Forms are a great way of ensuring that the data a user adds to the worksheet meets certain requirements and they can stop users from deleting vital information. You can add security to worksheets to ensure users can only access worksheets via the forms you create and thus increase security on your worksheets. Forms can also use data validation to make sure that the data entered meets the requirements of the worksheet.

For advanced Excel tutorials and courses, sign up for the Excel 2013 For Dummies Video Training, Deluxe Edition course and join over a thousand students who are learning to harness the power of Excel. This course offers over 106 lessons and 19 hours of content that will take you from Excel beginner to Excel master at your own pace. You will learn how to launch Excel and how to navigate your worksheets. You will learn how to enter data and use AutoFill to enter data automatically. You will learn to use and print formulas. Once you have mastered the basics, you will learn about pivot charts and tables and how to filter and sort data. The course also includes VBA and includes a VBA cheat sheet.