Grouping Option Buttons

There are two kinds of option buttons that you can put on a worksheet, ActiveX options buttons and Forms toolbar option buttons. The technique to group them so that certain option buttons act as a unit is different depending on the type used.

ActiveX

Option buttons from the Control Toolbox have a GroupName property which determines which of the option buttons work as a group. When you add an option button, the GroupName property is set to the worksheet’s name. This means that all option buttons added to a worksheet will work as one unit if you don’t change the GroupName. Note that the GroupName property is set to the worksheet’s name at the time the control is added. If you change the worksheet’s name in between adding option buttons, they will have different GroupName properties. In the example below, option buttons 1 and 2 have the GroupName “Group1? and option buttons 3 and 4 have the GroupName “Group2?. I just made those GroupNames up, there’s really nothing special about them. That means that you can use any GroupName that you want, as long as you know that matching GroupName’s act as one group – even when the abscence of a GroupName matches.

GroupOpt1

Forms Toolbar

To group option buttons from the Forms Toolbar, you have to place them in a GroupBox, also found on the Forms Toolbar. All option buttons in a GroupBox will act as one unit, and all option buttons outside of any GroupBox will act as one unit. In the example below, option buttons 7 and 8 are in one GroupBox and option buttons 9 and 10 are in another, which allows each pair to work independently of each other. Changing the CellLink of the option button will change the CellLink for every option button in that group.

GroupOpt2

Posted in Uncategorized

37 thoughts on “Grouping Option Buttons

  1. Dick, I’ve often wondered why there are both ActiveX and Forms controls. Could you maybe explain why there are two different kinds, and what the benefits of each are/when to use them?

  2. Kevin,

    I remembered reading the below in a newsgroup thread. It’s part of a response by Tom Ogilvy.

    “ActiveX (msforms 2.0 controls/controls from the Control Toolbox Toolbar)
    controls are designed to be placed in a container object. On userforms,
    this container object is the control object. On worksheets it is the
    OleObject. The ActiveX controls inherit certain properties from the
    container. For example on a Userform, the option button will have an exit
    event – but this is provided by the control object. On a worksheet it does
    not have an exit event because the oleobject container does not supply such
    an event. (Note that there is no separate physical action required to place
    the control in a container – the container is created when you place the
    control).

    The forms controls are completely separate controls and have their own way
    of operation. To an extent, MS has tried to make the msforms 2.0 controls
    have similar properties when used on a worksheet. However, the two sets of
    controls are different and operate somewhat differently. There is no reason
    that they should operate the same. Forms controls are part of the Excel
    object model. MSforms 2.0 controls are part of the MSforms 2.0 object
    model. forms controls can only be hosted in Excel. MSForms 2.0 controls
    can be hosted across the office applications and probably on other apps that
    support COM as well (don’t know if there are restrictions or not).”

    (Doug again) In addition, I’ve seen it stated a few times that Forms controls, though less powerful, are more reliable than ActiveX. J-Walk has a comparison at this link http://www.j-walk.com/ss/excel/tips/tip84.htm

  3. Thank you. I have been trying to figure out how to group form buttons for 2 hours. Microsoft Help did not provide me with the information I needed via database search.

  4. I have been doing something that I wonder if it is the same thing you are doing here for the grouping. I put the group box around the buttons like you have done, but then I go into design mode. I then select all the objects by holding down the Ctrl button on my keyboard and clicking on their edges. Once I have all of the objects selected I release the Ctrl button and right click over the objects and choose Grouping and then Group. Just curious as to if the grouping you are refering to is the same one I am using?

  5. Sorry forgot to mention you have to right click on the edge were your cursor turns into four arrows. Otherwise you lose selection.

  6. hey, guys… I’m wondering if you could help on this one… I have 3 questions with options buttons grouped using group box.. I wonder how I can get back to non selection of the option button (by the way its a questioner I’m making). I got the option button worked (running smooth) but whenever 1 session is done I cannot get back to non selection of option button or cannot set to blank option buttons..I have do it manually to re arrange selection. thank you

  7. Hi Leslie

    Right click the option button, choose ‘Format Control’, and then the Control tab. Changed from ‘Checked’ to ‘Unchecked’..

    a bit time consuming to do individually, but the only way I know of.

    Cheers – Niamh

  8. I just did a little test. I created a series of option buttons from the forms menu, which refer to a linked cell. As I selected different buttons, the cell link changed accordingly. To deselect all of the option buttons, I cleared the linked cell. Entering a zero also deselected all option buttons.

    Much easier than one-by-one selecting “unchecked” for the selected option button (which incidentally entered a zero in the linked cell, which is a good thing to notice).

  9. I have been playing with grouping option buttons via forms for 3 weeks and for some reason the cell links kept getting confused – foolishly I realised the group boxes have to be a certain distance apart otherwise it doesnt see the individual groups of option buttons and wont work. How stupid did I feel when I realised the problem!!!

  10. How do I link the option buttons to a cell so that which ever button is selected, a formula (like a commission) is calculated accordingly.

    I.e. We have 2 commissions: 17.65% and 20%. On the estimate form, I’d like to be able to select which commission to calculate. I have been able to create the option button choices, but don’t know how to link it to calculate automatically/correctly.

    Thanks in advance.

  11. Does anyone know of a way in which to change the GroupName property of a set of Option Buttons using VBA. The process appears as though it should be simple but as soon as I try to even read the GroupNames property of an OptionButtton object, let alone edit it, I get an error message which says that the object doesn’t support this property or method. Any ideas since I have a set of questionnaire sheets to set up with 150 buttonsd on each sheet and I don’t relish the prospect of having to change the GroupNames manually!

  12. Howard… Please post your questions in one of the on line forums… That being said give this a try…

    Sub test()
    Dim ctl As OLEObject

    For Each ctl In OLEObjects
    MsgBox ctl.Object.GroupName
    Next ctl
    End Sub

  13. OK Jim I’ll post it there. Your suggestion is pretty much how I tried to tackle it when I got the error message that I quoted. Thanks anyway.

  14. Jim,
    Could you please explain why you suggest that Howard post his question to the newsgroup? I thought his question (and your response) were completey in context.
    And thanks for your response – I always struggle with how to manipulate shapes and OLEobjects on the sheet using VBA.

  15. Howard

    I had the same problem in addressing the properties of a Option button.This works:

    ActiveSheet.OLEObjects(“OptionButton1?).Object.GroupName = MyGroup1
    ActiveSheet.OLEObjects(“OptionButton2?).Object.GroupName = MyGroup1
    ActiveSheet.OLEObjects(“OptionButton3?).Object.GroupName = MyGroup2
    ActiveSheet.OLEObjects(“OptionButton4?).Object.GroupName = MyGroup2

    etc.

  16. Thanks alot. I was going insane trying to that hehe
    This was a great help for my project.

  17. Thanks, I was trying to create two sets of radio buttons and didnt find any info on excel help. Then googled and reached here. Thanks Again. You saved my time.

  18. I have many option buttons in 3 seperate groups, after creating the second and 3rd group, I noticed I could select 2 buttons in a group. I only want 1 button per group to be able to be selected. How can I fix this problem?

    Thanks!!

  19. Is there any way of assigning activex option buttons to more than one group?

    I have a grid of option buttons (8 rows by 3 columns) and I want to be able to select any 1 in row one, 1 in row two, one in row 3,4,5,6 etc.. but only one in column 1, one in column 2 and 1 in column 3.

    I can group the rows or columns (or all) together easily but do not know how to restrict in the way I want.

    Any help much appricated.

  20. Ian –

    An option button has only one group, but you can write code responding to a button click that processes another group.

    Tedious, but it works.

  21. Excellent help, thanks! One last question: How can you make the “Group Box #” vanish when you view it onscreen? It must be possible, since frame certainly clutters up the view.

    I’ve made it disapper on the Print Preview by selecting the frame, right-clicking then selecting Format Object. Once there, I go to the Properties tab and unselecting Print Object. But that’s it so far.

  22. I’m not sure how to do it via the user interface, or even if it’s possible. You could do it in VBA using

    Sheet1.Shapes(“Group Box 1”).Visible = msoFalse
  23. @ Lisa
    assuming you don’t need to add more buttons in the future, you can delete the group box after you have grouped the buttons and they stay grouped.

  24. Could me someone help in the following problem?

    I have more grouped option buttons in an Excel sheet. These controls should maintain their places in relation to another objects (e.g.tables)in the same sheet, that means the should stay “anchored”.

    But if I open this sheet in an another PC (which has the same version of Excel and Win7) the mentioned controls occure in a shifted position in comparison to the coupled tables.

    I have already tried all possible settings in the menu of objects properties, but no one has helped.

    Many thanks for some helpful advice in advance.

  25. I’m trying the same as Peter does, but with an extra snack:

    Is it possible to “anchor” the active-X tool so that you can have it in every sheets.
    For example a command button for printing or saving so that you don´t have to return
    to another sheet to press a button…though I know that you can place it in the window…

  26. This is my first time using forms in Excel 2010, so any guidance you can provide is greatly appreciated. I inserted ActiveX option buttons and have set the properties GroupName to the same thing for each group of buttons. How do I get from the design mode into a user mode where I can test my form?

    I intend to distribute the form by email. These forms will be sent back to my Outlook account. Is there a way that Excel can be used to collect those responses?

  27. I have a fairly lengthy worksheet with about 1000 groupnames for the option buttons. I want to be able to copy the worksheet to begin a new dataset. I noticed when I copy the worksheet, data on one sheet affects the data on the other. I assume it is because the groupnames are the same. Is there a way to make multiple copies of the worksheet without having to go into the properties of each option button and change the groupname on each successive worksheet?

    p.s. this thread got me this far…thank-you…all!

  28. I have a group of ActiveX option buttons on a “template” worksheet. That worksheet gets copied multiple times within a workbook.

    My problem is that when I have more than one worksheet, all but one worksheet have all option buttons in the “false” state. I think it’s because all of the buttons are in the same group despite being on several pages.

    Is there a way to have the Option Button GroupName always be the same as the worksheet name…even if the worksheet is copied and/or changed?

  29. @Richard: There’s no event for copying a worksheet. However, if you have a volatile formula on your sheet, the Calculate event fires when you rename or copy a sheet. If you can hide a volatile formula somewhere on your sheet, you could use that.

    I put some CheckBoxes on a sheet and I put

    in a cell to trigger the Calculate event. For a

    , I put

    because

    is the name of the sheet. When I copy the sheet, the CheckBoxes on the new sheet all have a

    of

    .

    Also, when renamed

    to

    , all the CheckBoxes’

    changed to

    .

    If you do any of this in Design Mode, the Calculate event does not fire.


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.