Configuring and Using Forms created using ActiveX List Boxes and a Command Button in Excel 2007 and Excel 2010
Welcome to Part Two of my two part hub on Forms in Excel 2007 and Excel 2010. Forms allow you to enter data into an Excel spreadsheet, quickly, easily and most importantly accurately. In Part One (which can be found here http://robbiecwilson.hubpages.com/hub/Creating-Configuring-and-Using-Simple-Forms-in-Excel-2007-and-Excel-2010) I looked at using built-in forms which can be created from a range of column headings and are simple to use and configure. You can see an example of a form in Excel in the figure below:
In today’s hub, we will create a series of List Boxes from which a user can select one item. My example is from my (fictional) music shop. My form allows an order to be set up and then by clicking a command button the order can be copied to a new row. This allows subsequent orders to be added without earlier records being overwritten.
Creating List Boxes in Excel 2007 and Excel 2010
Before starting with our List Boxes, we need to ensure that the Developer tab is visible. If the tab is not visible,
For Excel 2007:
- Click on the Excel button
- Select Excel Options
- Select Show Developer tab in the Ribbon
- Click OK
If you are using Excel 2010:
- Browse to the File menu
- Select Options
- Click the Customize Ribbon tab
- Under Main Tabs tick Developer as shown below
Creating List Boxes is covered in greater detail in two of my hubs, the first on Form Controls boxes
and the second on ActiveX Controls boxes
To begin, we need to create six ActiveX List Boxes in a row (click the Insert button in the Controls group on the Developer tab and select List Box from the ActiveX section) and also six columns of data that our List Boxes will display:
Now we need to link the List Boxes to our data. To do this for the first List Box (Customer Number):
- Select Design Mode (click on the Design Mode Button in the Controls group on the Developer tab)
- Right click on the List Box and select Properties
- The two items we are interested in are LinkedCell and ListFillRange
- LinkedCell is the cell we want Excel to display the item that is chosen from the List Box. For this box, it will be B19
- ListFillRange tells Excel which items to list in the box and is B3:B6 in this example
Complete the remaining five List Boxes, linking them to the appropriate cells
The List Boxes are now complete. Exit out of Design Mode by clicking the Design Mode button as above and test your List Boxes. Clicking on an item in each List Box should add the selected item to the cell (row 19 in my spreadsheet) directly below the List Box.
Creating the Command Button in Excel 2007 and Excel 2010
Next, we have to create a command button so that we can use it to copy the data and move it to a new row so that it is not overwritten when you select a different item from the List Box. I also have a hub on creating command buttons which can be found here:
Using the same button as we did for the List Boxes (the Insert button on the Developer tab) create your button. Go into Design Mode again:
- Right click on your button and select Properties
- Select Caption and enter in a more appropriate caption (I used Press to create Record)
Information on the other configurable options available can be found in my hub above.
Adding Visual Basic to a command button using a macro in Excel 2007 and Excel 2010
The final stage is to add code to the command button so that it performs a function when it is pressed. Microsoft is very generous and allows us to all become Visual Basic programmers even if we know very little about Visual Basic. This allows us to do some very cool things using code without actually writing it. We do this by recording macros. I have a hub that explains how to do this in more detail which can be found here:
For our command button, we need code that will copy the results from our List Boxes and move this to a new row so that it is stored and not overwritten by any subsequent selections. Finally, the command button will clear the original row so that the user will not be confused by seeing the record appear twice in consecutive rows. Again on the Developer tab, we start by:
- Clicking the Record Macro button in the Code group on the Developer tab
- Note down the name of the macro we just created
- We then do exactly what we want our button to do; we Copy the contents of the cells B19:G19 and then right click on the cell B20 and select Insert Copied Cells
- Finally we clear the cells B19:G19
- Click the Stop Recording button
We have now captured the code that we need to assign to our button. Next we need to capture it and then add it to the button:
- Click the Macro button
- Select the macro with the name you noted down above
- Click Edit
You should see something similar to my code below
The part we are interested in is:
Application.CutCopyMode = False
Copy this and close the window that opened.
Now go back to your command button.
- Select Design Mode so that we can edit the button
- Select your button
- Right Click and select View Code
- Paste in your code leaving the line Private Sub CommandButton1_Click() and End Sub where they are (as the first and last line of the Visual Basic code respectively)
Note: If your button is not called CommandButton1 then ensure that this is changed in the first line to reflect your buttons name
You should end up with code similar to mine below:
- Exit Design Mode and test your button
Select each List Box to create a record and then click your button. Voila, your record is copied to the row below and the row cleared!
In this hub, we expanded on the Simple Form we created in Part One and created a form using six List Boxes, some Visual Basic code and a Command Button. This for me is what is exciting about Excel 2007 and Excel 2010. You can do so many things with the features available and when you combine them you can create a multitude of things limited only by your requirements of what you need Excel to do for you and your imagination.
Many thanks for reading; I do hope you enjoyed reading it as much as I enjoyed writing it. Please feel free to leave any comments you may have below!