ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

Configuring and Using Forms created using ActiveX List Boxes and a Command Button in Excel 2007 and Excel 2010

Updated on February 21, 2013

Introduction

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:

Example of a Simple Form created using Excel 2007 and Excel 2010.
Example of a Simple Form created using Excel 2007 and Excel 2010. | Source

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.

Form created using List Boxes, a Command Button, Visual Basic code and a macro in Excel 2007 and Excel 2010.
Form created using List Boxes, a Command Button, Visual Basic code and a macro in Excel 2007 and Excel 2010. | Source

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

Adding the Developer tab in Excel 2007.
Adding the Developer tab in Excel 2007. | Source

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

Adding the Developer tab in Excel 2010.
Adding the Developer tab in Excel 2010. | Source

Creating List Boxes is covered in greater detail in two of my hubs, the first on Form Controls boxes

http://robbiecwilson.hubpages.com/hub/How-to-Use-Create-and-Configure-Form-Controls-List-Boxes-in-Excel-2007-and-Excel-2010

and the second on ActiveX Controls boxes

http://robbiecwilson.hubpages.com/hub/Using-creating-and-configuring-ActiveX-Controls-List-Boxes-in-Excel-2007-and-Excel-2010

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:

List Boxes and data columns created to use in our Form in Excel 2007 and Excel 2010.
List Boxes and data columns created to use in our Form in Excel 2007 and Excel 2010. | Source

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:

http://robbiecwilson.hubpages.com/hub/Create-a-Command-Button-in-Excel-2007

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)

Example of a Command Button created using Excel 2007 and Excel 2010.
Example of a Command Button created using Excel 2007 and Excel 2010. | Source

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:

http://robbiecwilson.hubpages.com/hub/Guide-to-recording-Macros-in-Excel-2007

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

Example of Visual basic code gathered from a recorded macro to be added to a Command Button in Excel 2007 and Excel 2010.
Example of Visual basic code gathered from a recorded macro to be added to a Command Button in Excel 2007 and Excel 2010. | Source

The part we are interested in is:

Range("B19:G19").Select
Selection.Copy
Range("B20").Select
Selection.Insert Shift:=xlDown
Range("B19:G19").Select
Application.CutCopyMode = False
Selection.ClearContents

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:

Final code to be added to a Command Button in Excel 2007 or Excel 2010.
Final code to be added to a Command Button in Excel 2007 or Excel 2010. | Source
  • 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!

Conclusion

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!

Comments

    0 of 8192 characters used
    Post Comment

    No comments yet.

    Click to Rate This Article