Excel VBA Vlookup – Add a Form to Find Prices in Excel 2013

Visual Basic for Applications makes Excel 2013 more than just a spreadsheet for saving, sorting and analyzing data. VBA allows you to create small programs or routines within Excel that users can use to access the data in the worksheet. In this tutorial we will use VBA to create a form that allows users to look up the price and stock of a particular item quickly and easily using a custom user form. This tutorial is based on the assumption that you are used to working in Excel and that you know the basics of the application. If you would like to develop your Excel skills, Learn Microsoft Excel 2013 offers over 102 lectures and 3.5 hours of content will help you master the basics of Excel. The course will teach you all of the basic functions of worksheets and workbooks. You will learn about formatting your data and worksheets. You will learn all about Excel formulas, illustrations and you will learn to create your own charts. The course will teach you how to save, print and share your workbooks. Once you have the basics, you will learn about the advanced functions of Excel and how to use them.

To create a form that allows users to look up price and quantity you are going to:

1.       Create a custom user form

2.       Add a selection box to the form

3.       Add two output boxes to the form

4.       Add a “Find” Button

5.       Add a Lookup Prices button to the worksheet

Create a Custom User Form

We are going to use the following data for this tutorial:

To add a user form, click “Developer” and then click “Visual Basic” to open the VBA Editor. A new editor will open up.

To add a custom user form to the editor, select “Insert” and then click “UserForm”. A new form will be created in the editor window:

Now we will add an Item combo box to the form. We will use this box to allow users to select the item they are looking for. To create the combo box, we are using a named range in our worksheet called “Items.” To create a name range, select the items in your worksheet, right click and then select “Define Name” and name the range “Items.”

Now add a combo box to your form by selecting the combo box option and then dragging and drawing a combo box onto your new form:

In the properties box, rename the Combo box “ItemChosen” and change the Row source property to “Items”. This will load the Items list into the Combo box for the user to select an item.

Add a label called “Item” by adding a label from the toolbox and then changing the caption under the properties section to “Item”.

Add Two Output Boxes to the Form

Now that we have an input field, we are going to add two labels for the output based on the results of the Vlookup function. We will add 4 labels. Two labels will be descriptions and two labels will contain the results of the Vlookup function.

We will name label4 Item Stock by changing the name property to “ItemPrice”:

And we will name label5 “ItemStock” using the properties window.

Now add a “find” price button by selecting a command button from the toolbox and changing the caption to “Find Price:

Code the Find Price Button

Now we are going to code the button to use the Vlookup function to find the price and stock of an item that the user selects.

To code the Find Price button, double click the button to open the VBA editor.

The Vlookup function requires 4 arguments. We need to tell VBA what to search for, what range to search in, which column to use to return the value and whether we are looking for an exact match or whether we are looking for the closest match.

To search for the price the code will look like this:

ItemPrice = Application.WorksheetFunction.VLookup(ItemChosen, Range(“A1:C10”), 2, False)

We are assigning the answer to the label we named “ItemPrice.” So the value will be shown in the “ItemPrice” label field.

We then use the Vlookup function to find the value. Application.WorksheetFunction tells us what worksheet to use. Vlookup calls the function. “ItemChosen” comes from the combo box we created. Range(“A1:C10”) tells Excel where to search for the data. 2 refers to the column that contains the data. The price data is in column 2 in our worksheet and finally false means we are looking for an exact match.

We will now add a similar line of code to search for the amount of stock left per item:

ItemStock = Application.WorksheetFunction.VLookup(ItemChosen, Range(“A1:C10”), 3, False)

So your entire routine will look like this:

Now all we need to do is add a button to launch our form.

Add a Button

Choose insert from the Developer Menu and then select “Command Button” from the ActiveX controls. Double click on the button to launch the VBA Editor. Change the caption of the button to “Find Prices” in the control properties of the button:

Now add the following code to the button in VBA:

This will launch your form when the user clicks the button.

You are now ready to test your button and form. Close the editor and click the Find Prices button:

The following form should open:

When a user selects an item and clicks “Find Price”, the Price and Stock fields will automatically update:

Start Adding Forms to Your Worksheets

Adding forms and using worksheet is simple and adding the power of functions like the Excel VBA Vlookup function can really take your worksheets to the next level.

To learn more Excel skills and to become a master of Excel 2013, enroll in the VBA – Excel VBA and Macros (Part 2 of 2) course now and learn to take full advantage of the power of macros and VBA for Excel. This course offers you over 88 lessons and 8 hours of content designed to teach you how to use macros and VBA to create routines in your own spreadsheets and worksheets.