Welcome to my hub on creating a command button in Excel 2007. This hub is the last in my series in Excel 2007 based around check boxes and combo boxes. For those of you interested in creating and configuring check boxes and combo boxes in Excel 2007, please take a look at my hubs for both of these http://robbiecwilson.hubpages.com/hub/Configuring-and-Using-Check-boxes-in-Excel-2007 and http://robbiecwilson.hubpages.com/hub/Configuring-and-Using-Combo-boxes-in-Excel-2007 . I also introduce conditional formatting for Excel 2007 with specific examples around combo and check boxes http://robbiecwilson.hubpages.com/hub/Conditional-Formatting-in-Excel-2007 .
The reason for this hub was simple; I created a document with approximately 30 combo boxes which I used frequently. Once I had finished using the document I was faced with the prospect of having to “reset” each combo box which seemed like rather a waste of time. So I began to look at what Excel 2007 could do for me. As luck would have it, there is a solution. That solution is a Command Button (ActiveX Control). These buttons are extremely powerful and very useful. I will cover the creation and configuration of these buttons as well as the addition of some VB code for my specific use.
Creating a Command button
As with creating check boxes and combo boxes, to create a command button the Developer tab needs to be enabled. This is covered in my check box hub so to enable it click on the hub http://robbiecwilson.hubpages.com/hub/Configuring-and-Using-Check-boxes-in-Excel-2007 and follow the simple instructions contained there.
Once enabled, click on the Developer Tab and select Insert / Command Button (ActiveX Control). Using the cursor place and size your command button. The next step is to configure the command button.
Before beginning to configure the button, we need to discuss an important option on the Developer tab, Design Mode. With this deselected (as it is by default), Excel 2007 will enable your button for use. If you click on your button, it will not do anything as we have not given it a function yet. If you right click on it, there are no options available. To design or makes configuration changes to your button, we need to enter Design mode. The picture below shows Excel 2007 in Design Mode. Once you have completed the configuration, you must remember to deselect Design Mode to allow usage of your command button.
While in Design Mode, right click on your new button and select Properties. This will open up a box similar to that shown below.
Most of these options should be left as default. Let’s run through some of those you may wish to change or be aware of their function.
(Name) - This is not the label you see on the front of the button it is the name of the button itself and should be left as it is. If you change this label you will also have to change any Visual Basic code it uses or any references to it in Visual Basic or you will experience errors.
Backcolor – Changing the colour here changes the actual colour of your button from the default of grey.
Caption – If you want to change the name of your button from CommandButton One, this is the correct place to do it
Font – If you want to change the font of your button caption or label, then select your preferred font here
ForeColor – If you also wish to change the colour of your button caption or label then select your colour here
Picture – You may want to have a really fancy button with a picture on it, in which case you can select your picture using this option. Use PicturePosition to place it within your button
If you wish to resize your button to an exact size, use Height and Width to size it to your exact specifications. Top is used for specifying exactly how far from the top of the Excel spreadsheet your button is so that you can easily place them exactly where you wish.
Adding code to your new command button
So now you have created your brilliant new button, resplendent with funky colours and fonts and perhaps even a cool photo to boot. Now it is time to give your button a function to perform. To do this, ensure you are still in design mode, right click on your button and select View Code. Here is how it will look when you open it for the first time.
To enable our button to reset combo box drop down lists, we can ignore everything on the left and concentrate on the part labelled Book 1 – Sheet1(Code). Depending on what you need your button to do, the code you need to instruct your button goes into this screen. I need my button to reset all my combo boxes to N/A. The command that I have used for my button is
ActiveSheet.Shapes("Drop Down 220").ControlFormat.Value = 2
ActiveSheet.Shapes("Drop Down 222").ControlFormat.Value = 2
ActiveSheet.Shapes("Drop Down 223").ControlFormat.Value = 2
To explain the code the first line just tells Excel to reset DropDown 220 (the name assigned to my first combo box) to Value 2 which in my case is N/A.
Be sure to leave the Private Sub CommandButton1_Click() as the first line and End Sub as the last so that Visual Basic knows what to do at the beginning and end of your code. If you have made any mistakes you will get an error when you close it and also when you click on your button outside of design mode.
To find out the number for your DropDown values for each of your combo boxes, right click on a combo box and click Assign Macro. Within the macro name field there will be a number, in my example that number is 224. Continue this for all of your combo boxes and have a line for each. Here is how mine looks with my completed and working code.
So now you will have a button that resets any combo boxes you have back to the default of your choosing!
Command Buttons allow you to run a Visual Basic code when the button is pressed. This code can perform any number of functions; in this hub I used it to reset all my combo boxes drop down menus to their default settings to ready my document for its next use. I hope that you found this hub useful. Good luck with your adventures using Excel 2007! Please feel free to leave any feedback or comments you may have below.
I also have a number of other hubs on aspects of Excel 2007, covering everything from Conditional Formatting to creating charts and graphs. I have an Index hub which also covers how I successfully transitioned from Excel 2003 to 2007 as well as outlining my other Excel 2007 hubs which can be found here