Option Button Scores With CHOOSE Function

You can use option buttons on a worksheet, to make it easy for people to pick just one item from several options. There’s no programming required – just set up a group box, add option buttons, and link the buttons to a cell. The video at the end of this post shows you those steps.

The option buttons send a value to their linked cell, and we’ll take a look at how you can use those numbers.

Option Button Scores with CHOOSE Function http://blog.contextures.com/

Option Button Numbering

When you add the option buttons, Excel automatically gives each one an index number, within its group. To see an index number, click one of the option buttons, and its index number appears in its Linked Cell.

TIP: Right-click on an option button to select it, and you’ll see its Linked Cell’s address in the formula bar. In this example, the option buttons are linked to cell B1.

choosefunction05

In the screen shot below, I added the Lunch and Dinner options first. Later, I added an option button for Breakfast, and moved it to the top of the group box.

Even though Breakfast appears to be the first option button in the group, when I click that option, it puts a “3” in the linked cell (B1). Excel indexes them in the order that they were created.

choosefunction03

Can’t Change the Index Numbers

You can’t change the index numbers for the existing buttons, but Excel can change them automatically. For example, if I delete the Dinner option (currently #2 in the index), the Breakfast option moves up to #2 in the group.

Keep that in mind if you’re deleting and adding option buttons in a group. If you’re going to use the index numbers in a formula later, be sure that you know what those numbers are!

choosefunction04

Return a Different Number

Even though you can’t change the option button index numbers, you can use a formula to return a different number.

On one of my previous posts about option buttons, a commenter asked how they could give a zero value to one of the option buttons:

“Is there a possibility to include in the group the N/A point and have 0 for it at final result?”

Excel automatically numbers the option buttons starting with “1”, so we’ll need a formula to convert those index numbers to a different set of values.

Use the CHOOSE Function

In that article, I showed how to use a lookup table and the INDEX function, to assign a score to each option button value. Maybe the commenter couldn’t see how to add a zero, or found that setup too complicated.

For an easier solution, you could use the CHOOSE function. I created new buttons in the correct order, then added a “None” option to the Meals. Guests will be charged $5 for breakfast, $10 for lunch and $15 for dinner. If they select None, the cost will be zero.

In cell C1, enter this formula:

=CHOOSE(B1,5,10,15,0)

Click on one of the meal options, and its cost appears in cell C1.

choosefunction06

How It Works

The CHOOSE function returns a specific numbered item from a list.

=CHOOSE(B1,5,10,15,0)

  • If Dinner is selected, cell B1 contains the value 3.
  • The formula has a list of 4 items — 5,10,15,0
  • The 3rd item in that list is 15, and that is the cost of Dinner.

If the “None” option button is selected, its value is 4, so the 4th list item (0) will be returned.

choosefunction07

Note: The list items in the CHOOSE formula can be ranges too, so you could enter values on a worksheet, and refer to those cells. For example, =CHOOSE(B1,E1,E2,E3,E4)

Download the Sample File

To see how the option buttons and formulas work, you can download the sample file. On the Excel Sample Files page, go to the Functions section, and look for FN0040 – Option Button Choose Scores. The file is in xlsx format, and does not contain macros.

Video: Set Up Group Box and Option Buttons

Watch this video to see the steps for setting up a group box and option buttons.

______________

Option Button Scores with CHOOSE Function http://blog.contextures.com/

______________

Save

You may also like...

3 Responses

  1. Claude Van Horn says:

    For a Zero value, I would just subtract a 1 from the selected index value.

    I just used the Option buttons today in a Metrics report to select a Page Field in a Pivot Table based on a “Week Ending” page field. I had an HLOOKUP table with the most recent dates in it, I used the Option Index number to convert the 1 to 6 Index numbers to the week dates, with number 6 being an IFERROR choice of 99999.

    I used a Calculate event and a cell on the Pivot Table page to update the Page Field whenever the Date Value changed in my Target Cell of F5. In another part of the sheet, I pre-set the Page Field to “(All)” whenever a new Agent’s name was selected.

    It was necessary to use a Variant as the FieldVal variable because it had to take on the Date values as numbers (Formatted as Dates) or the “(All)” to use all dates. I had to struggle with formatting everything so I didn’t get errors, but this way worked best.

    Here is the code I used on the Sheet containing the Pivot Table. I declared FieldVal outside the Subroutine because I needed to set and reset it in other modules. I had to use the Calculate() event because I was changing the date on other pages instead of directly in the F5 cell.:

    Dim FieldVal As Variant
    Private Sub Worksheet_Calculate()
    Application.EnableEvents = False
    Dim pvt As PivotTable
    Dim wks as Worksheet
    Set wks = Sheets(Sheet1)
    Set pvt = PivotTables(“PivotTable1”)
    Set pg = PivotFields(“FieldName”)
    If FieldVal Range(“F5”) Then

    wks.pvt.pg.CurrentPage = Range(“F5”).Value

    WeekVal = Range(“F5”)

    End If
    Application.EnableEvents = True
    End Sub

  1. October 12, 2015

    […] Option Button Scores With CHOOSE Function […]

Leave a Reply to Debra Dalgleish Cancel reply

Your email address will not be published. Required fields are marked *