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.
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.
Kevin:
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?
10 August 2004, 2:58 pmDoug Glancy:
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
10 August 2004, 10:03 pmRamona Wallace:
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.
17 October 2005, 3:22 pmJerry W:
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?
26 November 2005, 10:15 amJerry W:
Sorry forgot to mention you have to right click on the edge were your cursor turns into four arrows. Otherwise you lose selection.
26 November 2005, 10:19 amLeslie Hanley:
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
23 December 2005, 8:48 pmNiamh O C:
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
3 January 2006, 5:42 amJon Peltier:
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).
4 January 2006, 5:36 amJ Gates:
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 December 2006, 5:59 pmTom Coppola:
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.
13 August 2007, 1:39 pmHoward Dore:
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!
22 November 2007, 10:19 amJim Thomlinson:
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
22 November 2007, 12:50 pmMsgBox ctl.Object.GroupName
Next ctl
End Sub
Howard Dore:
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.
22 November 2007, 5:20 pmAlex J:
Jim,
22 November 2007, 9:28 pmCould 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.