Clear all active option buttons from my worksheet

[brites]

Hi all,
I'm having a hard time making this work.
I have a workbook with multiple worksheets with the same pattern: they all have four Option Buttons (Form Controls not ActiveX), I'm trying to write a macro to unselect all of them in all worksheets.
Unfortunately, they do not have the same names across all sheets so I added a workaround solution... I am able to clear the first sheet, but somehow my code is not running all workbook...
That's what I'm doing:

---------
Sub Clearoptbut()
Dim ws As Worksheet
Dim i As Long
Dim opt As String


For Each ws In ActiveWorkbook.Worksheets
For i = 5 To 12
opt = "Option Button " & i
On Error Resume Next
ws.Shapes.Range(Array(opt)).Select
On Error GoTo 0
With Selection
.Value = xlOff
' .LinkedCell = ""
' .Display3DShading = False
End With
Next
Range("c15").ClearContents
Range("a1").Select
Next ws
End Sub
--------

Any help?
Thanks!

 

[Fluff]

How about

Sub Clearoptbut()
Dim ws As Worksheet
Dim i As Long
Dim opt As String

For Each ws In ActiveWorkbook.Worksheets
   For i = 5 To 12
      opt = "Option Button " & i
      On Error Resume Next
      ws.Shapes(opt).OLEFormat.Object.Value = xlOff
      On Error GoTo 0
   Next
   Range("c15").ClearContents
   Range("a1").Select
Next ws
End Sub

 

[dmt32]

Hi,
try

Sub Clearoptbut()
    Dim sh  As Worksheet
    Dim i   As Long
    For Each sh In ThisWorkbook.Worksheets
     For i = 1 To sh.OptionButtons.Count
        sh.OptionButtons(i).Value = xlOff
     Next i
    Next
End Sub

Dave

 

[brites]

How about

Sub Clearoptbut()
Dim ws As Worksheet
Dim i As Long
Dim opt As String

For Each ws In ActiveWorkbook.Worksheets
   For i = 5 To 12
      opt = "Option Button " & i
      On Error Resume Next
      ws.Shapes(opt).OLEFormat.Object.Value = xlOff
      On Error GoTo 0
   Next
   Range("c15").ClearContents
   Range("a1").Select
Next ws
End Sub

Like a charm!
Thanks!

 

[brites]

Hi,
try

Sub Clearoptbut()
    Dim sh  As Worksheet
    Dim i   As Long
    For Each sh In ThisWorkbook.Worksheets
     For i = 1 To sh.OptionButtons.Count
        sh.OptionButtons(i).Value = xlOff
     Next i
    Next
End Sub

Dave

Hey Dave, worked like a charm!
What an elegant solution...

Can I always use the Control Type like you did (sh.OptionButtons(i), or OptionButtons.Count ?
I had tried something like that but failed...

And I never knew I could only point to the sheet and not having to "Activate" it... Learned a lot here!
Thanks!

P.s.
As we are here... if this is a different post let me know and I'll do it:
How can I change the .LinkedCell = "" to a reference written on cell "E1"? Is that possible?

 

[brites]

Hi,
try

Sub Clearoptbut()
    Dim sh  As Worksheet
    Dim i   As Long
    For Each sh In ThisWorkbook.Worksheets
     For i = 1 To sh.OptionButtons.Count
        sh.OptionButtons(i).Value = xlOff
     Next i
    Next
End Sub

Dave

Hey Dave, worked like a charm!
What an elegant solution...

Can I always use the Control Type like you did (sh.OptionButtons(i), or OptionButtons.Count ?
I had tried something like that but failed...

And I never knew I could only point to the sheet and not having to "Activate" it... Learned a lot here!
Thanks!

P.s.
As we are here... if this is a different post let me know and I'll do it:
How can I change the .LinkedCell = "" to a reference written on cell "E1"? Is that possible?

Already got it here... thanks for the insight:

Dim lnk As String
lnk = Range("E1")
Dim sh As Worksheet
Dim i As Long
For Each sh In ThisWorkbook.Worksheets
For i = 1 To sh.OptionButtons.Count
sh.OptionButtons(i).LinkedCell = lnk
Next i
Next

 

[dmt32]

Hi,
You seldom need to activate a worksheet for code to work with it.
Glad you managed to figure out your additional requirement & that we were able to help & resolve your issue.
We appreciate the feedback

Dave