Changing Option Buttons status

[Fan924]

I have 3 Option Buttons on a sheet. Can I set the true/false status of
the buttons using a macro from another sheet?

 

[Dave Peterson]

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

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

If the optionbuttons are from the Forms toolbar:

With Worksheets("othersheetnamehere")
.OptionButtons("Option Button 1").Value = xlOff
.OptionButtons("Option Button 2").Value = xlOff
.OptionButtons("Option Button 3").Value = xlOff
End With

 

[Rick Rothstein]

Assuming we are talking about ALL the OptionButtons on a worksheet, these
can also be reset without know the individual names...

Sub ResetFormsOptionButtons()
Worksheets("Sheet1").OptionButtons.Value = False
End Sub

Sub ResetActiveXOptionButtons()
Dim OptBtn As OLEObject
For Each OptBtn In Worksheets("Sheet1").OLEObjects
OptBtn.Object.Value = False
Next
End Sub

 

[Dave Peterson]

I think I'd be more careful with the second one--there may be other OLEObjects
on that sheet:


Sub ResetActiveXOptionButtons()
Dim OptBtn As OLEObject
For Each OptBtn In Worksheets("Sheet1").OLEObjects
If TypeOf OptBtn.Object Is MSForms.OptionButton Then
OptBtn.Object.Value = False
End If
Next OptBtn
End Sub

And I've seen where lots and lots (much more than the OP's 3) of optionbuttons
(from the forms toolbar) would cause that first routine to break. Looping
through all of them would work, though:

Dim OptBtn As OptionButton
For Each OptBtn In Worksheets("Sheet1").OptionButtons
OptBtn.Value = xlOff
Next OptBtn

 

[Rick Rothstein]

I think I'd be more careful with the second one--there may be other

OLEObjects
on that sheet:

Sub ResetActiveXOptionButtons()
Dim OptBtn As OLEObject
For Each OptBtn In Worksheets("Sheet1").OLEObjects
If TypeOf OptBtn.Object Is MSForms.OptionButton Then
OptBtn.Object.Value = False
End If
Next OptBtn
End Sub

Yeah, I forgot to put the TypeOf check in (again<g>). Thanks for noticing
that.

And I've seen where lots and lots (much more than the OP's 3) of
optionbuttons
(from the forms toolbar) would cause that first routine to break. Looping
through all of them would work, though:

Dim OptBtn As OptionButton
For Each OptBtn In Worksheets("Sheet1").OptionButtons
OptBtn.Value = xlOff
Next OptBtn

I did qualify my posting by saying "Assuming we are talking about ALL the
OptionButtons on a worksheet" as it was hard to tell from the OP's posting
what was on the sheet.

 

[Fan924]

    With Worksheets("othersheetnamehere")

        .OptionButton1.Value = False
        .OptionButton2.Value = False
        .OptionButton3.Value = False
    End With

Thanks Dave