Add Products to Excel Order Form

The summer is off to a good start here in Canada – we had perfect weather for our first long weekend of the season. My garden is almost ready, and I haven’t killed any of the plants yet!

Over the weekend, my Contextures website and blog were moved to a different web host and servers. It seems to have gone smoothly, but please let me know if you see anything strange. Well, stranger than usual. 😉

Click to Add Products to an Order

As you know, when you’re moving, you sometimes find interesting things in the back of the closet. While I was checking the website, I found this sample file, that lets you click to add products to an Excel order form.

On the Data Entry sheet, you can select a product category, from the drop down list.

When a category is selected, the Excel Worksheet_Change event code runs, and lists all the products in the selected category.

Worksheet Change VBA Code

The code uses an Advanced Filter to copy the products onto the Data Entry sheet.

In the list of products, click on any row, in column G, to add that product to the order form.

The Order Form Move Code

Another event code runs when you click on a cell in column G – the SelectionChange event.

It checks the column and row number of the cell that was selected, and checks for an entry in column A.

If the selection was in column G and the row number is greater than 6, and there is something in column A, an X is added to the cell.

Then, the MoveRow code runs, and it copies the selected row to the order form, in the row below the previous product ordered.

Here is the OrderForm sheet, with condiments and dairy products listed.

Future Enhancements to the Product List Order Code

When you go back an look at an Excel project, you can usually think of several things that would improve it. In this example, I’d like to add code that prints the completed order form, and clears the OrderForm sheet.

Is there anything else that you’d add or change?

Download the Product List Order Workbook

To download the product list order form workbook, you can visit the Sample Files page on the Contextures website. In the Filters section, look for FL0016 – Move Items to Order Form.

The file is in Excel 2003 format, and zipped. It contains macros, so enable those, if you want to test the code.

_________________