Programming in Excel for Absolute Beginners

Programming in Excel is simpler than you may think. The VBA editor and built in programming functions in Excel make creating your own routines and programs easy. This tutorial will show you how to create your own Inventory Input form so that a user can add stock to the worksheet. This tutorial requires a basic knowledge of Excel. For a great introduction to Excel, enroll in the Microsoft Excel 2013 Basics course now. This course contains lessons that will teach you how to identify parts of the worksheet. You will learn how to select cells and enter data. The course will show you how to change column widths and row heights. You will learn to work with various elements of the worksheet and you will learn to work with charts.

Let’s start by creating our button to launch our form.

Programming in Excel – Creating a Command Button

Command buttons are a great way to launch programs you create for your Excel spreadsheets. They are also a great way to launch the VBA editor. To create a command button, click the Developer tab on the main Excel ribbon and select “Insert” and choose “Command Button” under the ActiveX controls.

Drag your Command button using the handles to size the button. When you are happy with the size and shape, make sure you have “Design Mode” clicked and then double click the button to launch the VBA editor panel within Excel.

Create the Stock Entry Form

We are going to create a Stock Entry form for our worksheet. To create a new form, select “Insert” and then select “UserForm” to insert a new user form:

When you add a new form, a new Toolbox opens to allow you to add your own controls to the form. This toolbox allows you to add labels, text boxes, combo boxes and other controls that you use to control the information on your form:

The form and each control or element on the form has properties. These properties allow you to change the appearance of the element as well as other properties:

Notice that in the above properties we have renamed the form to “StockForm.” It is important to name all of your elements with clearly defined names to make it easier when you start to create your Excel program.

Let’s add some controls and elements to our stock form.

Add the Input Boxes to Your Stock Form

Our stock form will allow users to add stock to an inventory form. Our form will require the user to enter the date, the user name, the item and the number of stock items so we will add input boxes for each of them.

To create an input box for the date, insert a text box and call it “newDate.” Insert a label for the text box and change the caption property of the label to date:

Create a combo box for the Item to be entered. A combo box will allow the user to either enter a stock item or choose a stock item from a drop down list. To create the drop down list, define the row source property of the combo box as a named range from your worksheet. For the tutorial we named the items as Inventory and then specified “Inventory” under the row source property:

Don’t forget to name the Item Combo box “newItem.”

Now add a combo box for the user. Change the user element name to “newUser” and use “Users” as a named range to create a row source for the users in the worksheet.

We will add a text box element for the number of stock items. Name the text box “newTotal.”

Now we can add another command button to allow users to add the items to the worksheet. Create a command button on the form. Double click the button to open the VBA editor for the button.

Sign up for the Excel 2013 for Dummies Video Training, Deluxe Edition course and take your skills from Excel beginner to Excel master. The Excel for Dummies course is a comprehensive course offering over 106 lessons and over 19 hours of content to make sure you are comfortable working in Excel. The course includes lessons on Excel VBA and even includes an Excel VBA cheat sheet.

Add the Code

To add each new inventory item to the list, we first need to find the next new row in our worksheet. If we don’t search for the next new row, then our button will add the information to the same line over and over again and overwrite the information.

To find the next new row, use the following code:

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

The above code finds the next row in a worksheet and stores the value in the variable called “newRow.” We are going to use this value to enter the new inventory using the following code:

Cells(newRow, 1).Value = newDate.Text

Cells(newRow, 2).Value = newUser.Text

Cells(newRow, 3).Value = newItem.Text

Cells(newRow, 4).Value = newTotal.Text

The above code inserts the values from the form into the cell with the address (newRow,1) to (newRow,4).

This is what our worksheet looks like:

When the user clicks the “Add Stock” button the following form will open:

The combo box works like this:

And when the user clicks add, a new row is added to the worksheet:

Add Forms to Your Worksheets

Forms are a great way to allow users to enter data into worksheets. Start learning how to harness the power of programming in Excel today. Join over four thousand students who are learning how to go from Excel beginner to pro with Learn Microsoft Excel 2013 – Advanced. This course offers over 52 lectures and 12.5 hours of content designed to teach you advanced Excel concepts. The course will teach you how to work with dates and times. You will learn about percentiles, averages and LINEST functions. The course will show you how to calculate depreciation and payments and you will learn to insert and format tables in your workbooks. You will be taught how to work with sparklines, data bars and pivot charts.