Change OptionButton true/false status on all sheets

[Fan924]

If you know the names of the optionbuttons (from the Control toolbox toolbar,
right?):

With Worksheets("sheet1")
.OptionButton1.Value = False
.OptionButton2.Value = False
.OptionButton3.Value = False
End With

Thanks to Dave Peterson, this works great. From any other sheet, I
can change the OptionButton's true/false status on sheet1. I have
multiple sheets (over 10) with the same three button, same names. I am
trying to step through each sheet and change them to the same true/
false status. I have the following "Sub Workbook_UnHide()" that steps
through all the sheets. I spliced....I failed. All I managed is a
stack overflow. Is there a better way to do this?


Sub Workbook_UnHide()
Dim s As Integer
Application.ScreenUpdating = False
For s = 2 To ActiveWorkbook.Worksheets.Count
ActiveWorkbook.Worksheets(s).Visible = True
Next s
ActiveWorkbook.Worksheets(1).Visible = False
Application.ScreenUpdating = True
'Application.EnableEvents = True
End Sub

 

[papou]

Hello
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
For i = 1 To 3
ws.OLEObjects("OptionButton" & i).Object.Value = False
Next i
Next ws

HTH
Cordially
Pascal

 

[Fan924]

PS
I have Excel97

 

[Fan924]

My code, something not quite right about it but I can't spot it.
[Excel97]
--------------------------------------------------------
Sub SelectSW3()
Dim s As Integer
For s = 2 To ActiveWorkbook.Sheets.Count
With Worksheets(s)
.OptionButton1.Value = False
.OptionButton2.Value = False
.OptionButton3.Value = True
End With
Next s
Beep
End Sub
--------------------------------------------------------
The error I am getting is
Run-time error '428':
object does't support this property or method

Debugger highlights this " .OptionButton1.Value = False "

 

[Dave Peterson]

Are all the sheets in the workbook worksheets--no chart sheets, no macro sheets.

Do all the worksheets from 2 to the last have Optionbuttons by those names?

I'm guessing that the answer to the second question is no.

My code, something not quite right about it but I can't spot it.
[Excel97]
--------------------------------------------------------
Sub SelectSW3()
Dim s As Integer
For s = 2 To ActiveWorkbook.Sheets.Count
With Worksheets(s)
.OptionButton1.Value = False
.OptionButton2.Value = False
.OptionButton3.Value = True
End With
Next s
Beep
End Sub
--------------------------------------------------------
The error I am getting is
Run-time error '428':
object does't support this property or method

Debugger highlights this " .OptionButton1.Value = False "

 

[Fan924]

Are all the sheets in the workbook worksheets--no chart sheets, no macro sheets.

Hi Dave, there are charts and macros on all sheets.

Do all the worksheets from 2 to the last have Optionbuttons by those names?

Yes. the names OptionButton1, OptionButton2, OptionButton3, are the
same on all sheets. Are there changer I can make to make this work?

 

[Fan924]

Sub SelectSW1()
Dim ws3 As Worksheet
Set ws3 = Worksheets("Page17")
'ws3.Range("A2").Value = "ECU1"
With Worksheets("AWOT17")
.OptionButton1.Value = True
.OptionButton2.Value = False
.OptionButton3.Value = False
End With
With Worksheets("Page16")
.OptionButton1.Value = True
.OptionButton2.Value = False
.OptionButton3.Value = False
End With
Beep
End Sub

If I list indivifual sheets like this, it works fine. A bit labor
intensive. The Workbook_UnHide() routine works fine stepping through
worksheets. When I spliced them together, I get probelems.

 

[Dave Peterson]

I was asking about chart sheets--not worksheets with charts. Same thing with
macro sheets--not sheets with macros. These are different things.

 

[Dave Peterson]

I think you'll find that if you included code for all the sheets (except the
first), you'll find the error.
You'll have one sheet that doesn't have at least one of those optionbuttons with
those names.

 

[Fan924]

Can I test for the presents of OptionButton1, OptionButton2, and
OptionButton3 on each sheet so I can skip that sheet and go to the
next?

 

[Dave Peterson]

Did you find the sheet that didn't have it/them?

If the names are wrong, then you may not end up with what you want.

But if you don't care, you can just do the work and ignore any error.

Sub SelectSW3()
Dim s As Long
For s = 2 To ActiveWorkbook.Sheets.Count
With Worksheets(s)
on error resume next
.OptionButton1.Value = False
.OptionButton2.Value = False
.OptionButton3.Value = True
on error goto 0
End With
Next s
Beep
End Sub