Excel 2010 - Can you change the scope of an Option Button Group

[MigrantFirmWorker]

I have a 2010 worksheet with a group of option buttons. If I duplicate the sheet, the OBs on both sheets function as one big group (i.e. if I select a button on
Sheet1, the buttons on Sheet2 are turned off and vice versa). It appears that all the buttons are scoped to the workbook rather than their worksheet.

The only documentation I could find on the MS site was for Excel 2003 and specifically stated that OB groups were scoped to the worksheet.

Is there a way in 2010 to change the scope of an OB group?

Thanx.

Chris

-------------------------------------------------------------
"Don't be deceived. We're all temporary employees.

 

[combo]

Actvex optionbutton controls have GroupName property that by default is their parent worksheet's name. When you copy the worksheet, the GroupName copies too. As it's workbook level property, optionbuttons stay linked.
To change the GroupName of a set of optionbuttons:
- switch to design mode,
- select optionbuttons that are to be grouped,
- go to vb editor, select worksheet's code module,
- change GroupName to any valid name,
- leave design mode.

combo

 

[MigrantFirmWorker]

Thanx for the reply. Unfortunately, renaming the groups is not an option. My users need to be able to duplicate the sheet at will without having to mess with the controls. I guess I can use checkboxes and for the radio button behavior with code but that just seems really ugly.

We just migrated from 2003 to 2010 and I don't recall this behavior in 2003. It seems that if MS changed the default behavior along the way, there would be a means to control it. Any ideas?

Chris

-------------------------------------------------------------
"Don't be deceived. We're all temporary employees.

 

[combo]

It's the same in excel 2003.
Maybe you were using forms controls rather than activex ones?
You can consider applying combobox or listbox instead. If there is a code behind, you could separate a code from the changing number of sheets.

combo

 

[strongm]

As combo says, the behaviour you describe in your OP applies to the MSForms activeX Option Button control (shape type msoOLEControlObject). The behaviour you describe as seeing in Excel 2003 is for the form control version (Shape type msoFormControl)

Assuming you do actually require the msoOLEControlObject version, combo's solution for changing the group seems reasonable - if you automate it. You just need to add something to the Activate event of the source sheet (which will, of course, get duplicated along with the option buttons). e.g:

[blue]Private Sub Worksheet_Activate()
    Dim wombat As Object
    Dim myOpt As MSForms.OptionButton
    
    For Each wombat In ActiveSheet.Shapes
        If wombat.Type = msoOLEControlObject Then
            If wombat.OLEFormat.progID = "Forms.OptionButton.1" Then
                Set myOpt = wombat.OLEFormat.Object.Object
                myOpt.GroupName = ActiveSheet.Name
            End If
        End If
    Next
End Sub[/blue]