Changing Option Buttons status
 

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

Changing Option Buttons status
 

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

Changing Option Buttons status
 

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" wrote in message
...
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

Changing Option Buttons status
 

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" wrote in message
...
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

Changing Option Buttons status
 

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)

Changing Option Buttons status
 

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

Thanks Dave