Enabling/Disabling multiple option buttons

[Will]

I have an excel worksheet ("Sheet 1") with a number of Optionbuttons
all of which I want to disable
via macro which runs from another worksheet ("Sheet 2").

So I want to do something like this:

For i = 1 To 7
Sheets("Sheet 1").Controls("OptionButton" & i).Enable = False
Next i

Will this work?
Or am I missing something?

 

[Mike H]

Maybe

Sub Switch_Em()
Dim ws As Worksheet
Dim obj As OLEObject
Set ws = Sheets("Sheet1")
For Each obj In ws.OLEObjects
If TypeName(obj.Object) = "OptionButton" Then
obj.Object.Value = False
End If
Next obj
End Sub

Mike

 

[Rick Rothstein]

You didn't say where your OptionButtons came from (the Forms or Controls
Toolbox toolbars). If from the Forms toolbar, you can disable them all with
a macro containing just a single line of code...

Sub DisableFormsOptionButtons()
Worksheets("Sheet 1").OptionButtons.Enabled = False
End Sub

If they are from the Controls Toolbox toolbar, then this macro will do what
you want...

Sub DisableActiveXOptionButtons()
Dim OptBtn As OLEObject
For Each OptBtn In Worksheets("Sheet 1").OLEObjects
OptBtn.Object.Enabled = False
Next
End Sub

And if you both and want to disable them all, then use this macro...

Sub DisableAllOptionButtons()
Dim OptBtn As OLEObject
With Worksheets("Sheet 1")
.OptionButtons.Enabled = False
For Each OptBtn In .OLEObjects
OptBtn.Object.Enabled = False
Next
End With
End Sub

 

[Will]

Thanks for your help Rick.
All my OptionButtons come from the ActiveX Controls toolbox.

Another question:
If I don't want to disable ALL Optionbuttons, but only a subset.
-- for example OptionButton10 to OptionButton20.

Is this possible?
Is there a For ... Next routine I can use?

Thanks,
Will

 

[Dave Peterson]

Dim OLEObj As OLEObject
Dim iCtr As Long
For iCtr = 10 To 20
Worksheets("Sheet1").OLEObjects("OptionButton" & iCtr).Enabled = False
Next iCtr