On Jul 9, 5:32 am, Dave Peterson wrote:
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

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" a écrit dans le message de news:
...
On Jul 9, 5:32 am, Dave Peterson wrote:
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

PS
I have Excel97

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 "

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.

Fan924 wrote:

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

On Sep 18, 5:33*am, Dave Peterson wrote:
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?

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.

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



Fan924 wrote:

On Sep 18, 5:33 am, Dave Peterson wrote:
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?

--

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 wrote:

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

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?

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

Fan924 wrote:

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