ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

Create a Command Button in Excel 2007

Updated on August 22, 2012

Introduction

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.

Configuration

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!

Conclusion

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

http://robbiecwilson.hubpages.com/hub/How-to-adjust-to-Excel-2007-from-previous-versions-as-well-as-step-by-step-guides-to-many-functions-in-Excel-2007


Comments

    0 of 8192 characters used
    Post Comment

    No comments yet.

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, hubpages.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://hubpages.com/privacy-policy#gdpr

    Show Details
    Necessary
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Marketing
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Statistics
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)