ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

How to create, configure and use Form Controls and ActiveX Controls Spin Buttons in Excel 2007 and Excel 2010

Updated on February 10, 2013

Introduction

Welcome to my latest hub for Excel 2007 and Excel 2010. Today, I will be looking at the Spin Button. Spin Buttons allow you to create a button so that users can quickly select a number from a range using an up or down arrow. In my example today, we create some spin buttons to allow users of my spreadsheet to select their date of birth.

Example of a Spin Button created in Excel 2007 and Excel 2010.
Example of a Spin Button created in Excel 2007 and Excel 2010. | Source
  • Form Controls Spin Buttons are simpler to configure and are recommended ahead of ActiveX buttons.
  • ActiveX allows you to add Visual Basic scripts to your button and also allows you to change its colours as well as a number of other advanced features.
  • ActiveX buttons are not compatible with Macs.

Creating a Form Controls Spin Button in Excel 2007 and Excel 2010

Before creating a new Spin Button, we need to ensure that the Developer tab is available in Excel. If it is not available and you use Excel 2007:

  • Click on the Excel button
  • Select Excel Options
  • Select Show Develop tab in the Ribbon
  • Click OK

Adding the Developer tab to Excel 2007.
Adding the Developer tab to 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 to Excel 2010.
Adding the Developer tab to Excel 2010. | Source

Now that the Developer tab is visible:

  • Click the Insert button in the Controls group
  • Select Spin Button in the Form Controls section
  • The cursor will change to a cross, select the outline of your Spin Button

Configuring a Form Controls Spin Button in Excel 2007 and Excel 2010

Now we need to configure our new Spin Button:

  • Right click on it and select Format Control
  • On the Control tab, first select the Minimum value. The first Spin Button will allow the user to select the day they were born so the minimum value is 1
  • For Maximum value we choose 31
  • Incremental change is the number that the value will increase by, which in this case is 1
  • Finally, select the Cell link which is the cell that the number created by the Spin Button will appear in
  • You can also select 3-D shading which adds as you would expect, 3-D shading to the button

Configuring a Form Controls Spin Button in Excel 2007 and Excel 2010.
Configuring a Form Controls Spin Button in Excel 2007 and Excel 2010. | Source

To create additional buttons, right click your Spin Button and then Copy and Paste to create two new buttons. Configure them as above for your months and years.

To allow the results of your three spin buttons to display the user’s birth date above your buttons:

  • Ensure that the Cell link option is configured correctly so they are in the correct order
  • I then added a column in between each cell and added a /
  • Then re-size the three new columns to the width of the /
  • Next, re-size the columns containing the day, month
  • Finally, I changed the alignment of the cells, using the buttons in the Alignment group on the Home tab. I left aligned the day and right aligned the month and year to bring them together
  • I then added a border and re-coloured the cells by selecting them, right clicking and selecting Format Cells and changing the cells using the options on the Fill and Border tabs


Aligning your Spin-buttons in Excel 2007 and Excel 2010

When you use copy and paste (or even if you create new buttons from scratch) it is unlikely that your buttons will align exactly. To ensure that they line up perfectly:

  • Hold down the Control key and left click select all the Spin Buttons you want to align.
  • You will see that the buttons are all selected as below

How to select multiple Spin Buttons in Excel 2007 and Excel 2010.
How to select multiple Spin Buttons in Excel 2007 and Excel 2010. | Source
  • Select the Align button on the Page Layout tab in the Arrange group
  • Use Align Top and Align Bottom to line the buttons up horizontally
  • To ensure that the gaps between them are even, select Distribute Horizontally

Creating ActiveX Controls Spin Buttons in Excel 2007 and Excel 2010

Creating an ActiveX button is identical to how we created a Form Controls button, except that we choose the Spin Button in the ActiveX Controls section via the Insert button on the Developer tab in the Controls group.

Note: to configure your ActiveX button, you must be in Design mode. To enter / exit Design mode, select the Design Mode button which is next to the Insert button we used to create our Spin Button.

Now that we are in Design mode, right click on your button. There are two more options available when compared to Form Controls buttons:

Right click and select Properties which opens up some advanced options available as shown below

Most of these can be safely left as default:

  • Forecolor and Backcolor change the colour of your button
  • You can also change the mouse pointer or the icon using MousePointer and MouseIcon respectively.
  • ViewCode allows you to add Visual Basic code to your button
  • Format Control allows you to configure your button in the same manner we configured our Form Controls button

Configurable options for an ActiveX Controls Scroll Buttons in Excel 2007 and Excel 2010.
Configurable options for an ActiveX Controls Scroll Buttons in Excel 2007 and Excel 2010. | Source

Conclusion

Spin buttons allow you to create buttons so that users can select numbers from a range quickly and easily. In my example today, we looked at creating three spin buttons so that users can select their birth date. We also investigated the difference between the simpler Form Controls button and the more complex ActiveX button. We also learnt how to align buttons and how to create a number of buttons quickly and easily.

Alongside this hub on Spin Buttons, I have also written a number of hubs that investigate the other controls available in Excel 2007. These include:

Example of a Combo Box created using Excel 2007 and Excel 2010.
Example of a Combo Box created using Excel 2007 and Excel 2010. | Source

Combo Boxes: allow users to select options from a drop down list that is fully configurable. Combo boxes are useful for short lists such as zodiac signs or inventories. My Combo Boxes hub can be found here:

http://robbiecwilson.hubpages.com/hub/Configuring-and-Using-Combo-boxes-in-Excel-2007

Combo Box showing Conditional Formatting of the adjoining text created using Excel 2007 and Excel 2010.
Combo Box showing Conditional Formatting of the adjoining text created using Excel 2007 and Excel 2010. | Source

Check Boxes: such as To-Do lists or Shoping Lists or for Yes / No, On / Off lists which can be illustrated by selecting or deselecting check boxes. Conditional formatting can be used as well to change the text colour based on the result of check box. In addition, I have a hub for Check Boxes that can be found here.

http://robbiecwilson.hubpages.com/hub/Configuring-and-Using-Check-boxes-in-Excel-2007

Check Boxes using Conditional Formatting on the adjoining text created using Excel 2007 and Excel 2010
Check Boxes using Conditional Formatting on the adjoining text created using Excel 2007 and Excel 2010 | Source

Conditional Formatting: Both Combo boxes and Check Boxes are made even more useful when they are used together with Conditional Formatting. Conditional Formatting allows you to alter text or cells using rules so that as in the figures above when a task is completed the text for that task is turned green and when it is outstanding it is red or, it has a tick (or check) next to it when complete or a cross if it has not yet been completed. My hub detailing how to use and Conditional Formatting can be found here:

http://robbiecwilson.hubpages.com/hub/Conditional-Formatting-in-Excel-2007

Command Buttons: used to create a click-able button in Excel that is designed to perform a specific function using Visual Basic. In my hub, I created a reset button that resets the sheets combo boxes or check boxes to their original setting. My hub on Command buttons can be found here:

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

Example of a Command Button and the Visual Basic code used created in Excel 2007 and Excel 2010.
Example of a Command Button and the Visual Basic code used created in Excel 2007 and Excel 2010. | Source

Scroll Bars: a scroll bar allows a user to select a value by a number of means. They can click the arrows, drag the slide or click on the bar itself. My hub on scroll bars can be found here:

http://robbiecwilson.hubpages.com/hub/Creating_and_configuring_Form_Controls_and_ActiveX_Controls_Scroll_Bars_in_Excel_2007_and_Excel_2010

Examples of Scroll Bars created in Excel 2007 and Excel 2010. A Form Controls bar (above) and an ActiveX bar are illustrated.
Examples of Scroll Bars created in Excel 2007 and Excel 2010. A Form Controls bar (above) and an ActiveX bar are illustrated. | Source

I do hope that you enjoyed reading this hub and that you found it useful and informative. Please feel free to leave any comments you may have below. Many thanks for reading!

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)