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

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

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

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

--
Rick (MVP - Excel)


"Dave Peterson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
> Fan924 wrote:
>>
>> 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

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 wrote:
>
> 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
>
> --
> Rick (MVP - Excel)
>
> "Dave Peterson" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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
> >
> > Fan924 wrote:
> >>
> >> 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

--

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

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.


--
Rick (MVP - Excel)

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

Thanks Dave