Change OptionButton true/false status on all sheets

  • Thread starter Thread starter Fan924
  • Start date Start date
F

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
Click to expand...

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
 
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.
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 "
Click to expand...
 
Are all the sheets in the workbook worksheets--no chart sheets, no macro sheets.
Click to expand...

Hi Dave, there are charts and macros on all sheets.
Do all the worksheets from 2 to the last have Optionbuttons by those names?
Click to expand...

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.
 
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.
 
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
 
Back
Top