ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel
  • »
  • Technology»
  • Computers & Software»
  • Computer Software

How to use, create and configure Form Controls / ActiveX Controls Option / Radio Buttons in Excel 2007 / Excel 2010

Updated on February 26, 2013

Introduction

Welcome to my latest hub on Form and ActiveX Controls in Excel 2007 / Excel 2010. Today, we will investigate the Option Button (also known as the Radio Button). Option Buttons are perfect if you want users of your spreadsheet to select only one option from a list. The option that your user selects can then be stored for future use.

Form Control and ActiveX Control Option buttons are very similar; the ActiveX button allows you to change colours and fonts should you wish to. In addition, ActiveX buttons allow you to create groups of buttons, whereas Form Control buttons will be automatically added to any nearby groups and cannot be reconfigured. The below figure shows this difference, the right hand buttons are ActiveX (and have been assigned to different groups) and the user can select from two of the five options available, rather than one from the five options on the left (Forms Control buttons).

Form Controls (left) and ActiveX Controls (right) Option (Radio) buttons created using Excel 2007 and Excel 2010.
Form Controls (left) and ActiveX Controls (right) Option (Radio) buttons created using Excel 2007 and Excel 2010. | Source

In today’s hub, we will create and configure two buttons, one Form and one ActiveX button. Alongside creating the buttons, we will also convert the results of the user’s selection into something more meaningful (the default result of Form Control buttons is a number and from ActiveX it is true / false).

Option (Radio) Buttons created using Excel 2007 and Excel 2010.
Option (Radio) Buttons created using Excel 2007 and Excel 2010. | Source

Adding the Developer tab to Excel 2007 / Excel 2010

Before starting, we need to ensure that the Developer tab in available in Excel. If it is not, for Excel 2007:

  • Navigate to the Excel button
  • Click Excel Options
  • On the Popular tab, ensure that Show Developer tab in the Ribbon is selected

Adding the Developer Tab to Excel 2007.
Adding the Developer Tab to Excel 2007. | Source

If you are using Excel 2010:

  • Click on the File menu
  • Select Options
  • Choose the Customize Ribbon tab
  • Tick Developer under Main Tabs as shown below

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

Creating a Form Controls and ActiveX Option (Radio) Button in Excel 2007 and Excel 2010

To create a button:

  1. First navigate to the Developer tab and select the Insert button in the Controls Group and select Option Button in the Form Controls section.
  2. The cursor will change to a +
  3. Define the outline of your Option / Radio Button

Either right click and select Copy and then Paste further buttons, or repeat the three above steps to create additional buttons

The process of creating ActiveX buttons is identical, except you choose the Option Button from the ActiveX Controls section via the Insert button in the Controls Group instead.

Configuring a Form Controls Option (Radio) button in Excel 2007 and Excel 2010

Configuring a Form Controls button is quite straightforward.

  • To change the button’s caption right click the button itself and select Edit

All other configurable options are available via Format Control. To access these options:

  • Right click the button and select Format Control
  • The Colors and Lines tab allows you to change the Fill colour and the Line colour (creates a box around the button)
  • The Size tab allows you to precisely change the size of the button
  • The Control tab is where you configure the Cell link which is the cell in which Excel places the result of the user’s selection

Configuring a Form Controls Option (Radio) button using Excel 2007 and Excel 2010.
Configuring a Form Controls Option (Radio) button using Excel 2007 and Excel 2010. | Source

Configuring a ActiveX Controls Option (Radio) button in Excel 2007 and Excel 2010

ActiveX buttons have many more options available than Forms buttons. This additional complexity allows you to configure them far more than Forms buttons.

Note: in order to configure an ActiveX button, you must be in Design mode. To enter Design mode, click the Design Mode button in the Controls group on the Developer tab.

While in Design Mode, right click on your button and select Properties

Configurable options available in ActiveX Controls Option (Radio) buttons in Excel 2007 and Excel 2010.
Configurable options available in ActiveX Controls Option (Radio) buttons in Excel 2007 and Excel 2010. | Source

As you can see from the above figure, there are a large number of items you can configure. I will discuss those options which you either must change or should change if it fits your design for your own buttons:

  • BackColor – this allows you to change the colour of your button
  • Caption – used to change the text on your button
  • Font – select a different font here
  • ForeColor – font colour can be adjusted here
  • GroupName – if you want to create a separate group of buttons then you would choose a new name for the second group here
  • Linked Cell – this cell will display whether this button has been selected

Note: Unlike Form Control buttons, EACH ActiveX button in a group MUST each have their own linked cell.

Note: to change the size of the button, I recommend doing this via the Size tab by right clicking and selecting the Format Controls tab as this tab uses centimetres.

Working with the output from your Option (Radio) Buttons in Excel 2007 and Excel 2010

Now that we have created and configured our buttons, we need to translate Excel’s output into something that we can use or display. First, let’s look at exactly what Excel produces. In the figure below:

N2 = the output from the Form Control button (the light pink one)

N3 – N7 = the output from each of the ActiveX buttons (the yellow one) in order

Output from our Option (Radio) buttons created in Excel 2007 and Excel 2010.
Output from our Option (Radio) buttons created in Excel 2007 and Excel 2010. | Source

To translate this data into something we can use, we need to use IF statements.

=IF(N2=1,"Compact Disc", IF(N2=2,"Vinyl", IF(N2=3,"Mini Disc", IF(N2=4,"Cassette", IF(N2=5,"DVD")))))

Now the cell with this formula in it displays what the user selected rather than the number of the Option Button they selected.

=IF(N3=TRUE,"Compact Disc", IF(N4=TRUE,"Vinyl", IF(N5=TRUE,"Mini Disc", IF(N6=TRUE,"Cassette", IF(N7=TRUE,"DVD")))))

Formula changing the output from an Option (Radio) Button into more meaningful information in Excel 2007 and Excel 2010.
Formula changing the output from an Option (Radio) Button into more meaningful information in Excel 2007 and Excel 2010. | Source

Now we have two cells that contain the actual user’s selection that we can record or use as a reference later on.

Conclusion

Option (or Radio) buttons in Excel 2007 and Excel 2010 allow you to create panels with a list of items from which the user can select one item. The user’s selection is then recorded in a cell which can be stored or manipulated further.

In this hub, we investigated creating and configuring Form and ActiveX Control buttons. We also looked at creating groups of ActiveX buttons which allow the creation of discrete groups of buttons as well as other differences between these and the simpler Form Control buttons. Finally, we looked at using IF statements to translate the output from our buttons into more useful outputs.

In addition to this hub on Option / Radio buttons, I have a number of other hubs on the Controls available in Excel 2007 and Excel 2010. These include:

ActiveX Control List Box created using Excel 2007 and Excel 2010.
ActiveX Control List Box created using Excel 2007 and Excel 2010. | Source
Form Control List Boxes created using Excel 2007 and Excel 2010.
Form Control List Boxes created using Excel 2007 and Excel 2010. | Source

List Boxes: are ideal for short lists such as a list of postage options or signs of the zodiac. They allow users to select from items that are listed and selectable by the user. The list itself is fully configurable and the user can select one item if it is a Form Control box or multiple items if it is an ActiveX Control box. Users can also use Shift and Control to select more than one item at a time. My hubs on ActiveX and Form Control List Boxes are located here:

Toggle Buttons created in Excel 2007 and Excel 2010.
Toggle Buttons created in Excel 2007 and Excel 2010. | Source

Toggle Buttons: ActiveX Toggle bittons through the addition of Visual Basic code allow you to create a button that is ideal for turning things on and off in Excel. For example, you can turn grid lines or split screens on and off with the click of a single button. Click here to learn more about Toggle Buttons:

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


Form Control (top) and ActiveX (bottom) Scroll Bars created using Excel 2007 and Excel 2010.
Form Control (top) and ActiveX (bottom) Scroll Bars created using Excel 2007 and Excel 2010. | Source
Spin Button created using Excel 2007 and Excel 2010.
Spin Button created using Excel 2007 and Excel 2010. | Source

Spin Buttons and Scroll Bars allow users of your spreadsheets to quickly select values from a configured range. Spin buttons are excellent for selecting things such as dates of birth. Scroll bars are perfect for selecting numbers from ranges such as selecting an interest rate for a loan. Both are used heavily on the Internet so users now instinctively how to use both.

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

Comments

    0 of 8192 characters used
    Post Comment

    • profile image
      Author

      Robbie C Wilson 4 years ago

      Hi Jason,

      Many thanks for your kind comment, I am glad that you found the article so useful. Option Buttons when created are put into a default group based on the sheet they are in. To resolve the issue you had with the groups, create ActiveX buttons and go into the properties of the Option Buttons and put them into separate groups. Hope this helps and thanks again for reading!

    • profile image

      Jason Buda 4 years ago

      I like the versatility of the ActiveX Option Button, but when you copy the sheet the option Button takes on the same group as the prior one so a customized copy sheet macro would have to be used to get the option button group unique or else the previous sheet will lose the settings of the option button when you change the new sheet! I guess there is always a catch, huh? I appreciate your article. I learned something about the ActiveX buttons being more customizable, but I have a lot of applications where I need to duplicate the sheet. Thanks!

    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)