Option Button

[Karen]

Hello, I'm using Office 2000 (Excel) and I'm trying to
recreate a form from a hard copy. My computer version
needs to look exactly the same. In this form there are
circles that need to be filled in. I think I'm headed in
the right direction by using the control toolbar and
inserting option buttons since multiple circles may need
to be filled in. I'm able to get all of them filled in
but once they are filled in I can't get them to clear
(toggle from filled in to not filled in). Any help or
ideas for other ways to accomplish this task would be
greatly appreciated.

Karen

[Dave Peterson]

First, if you're using optionbuttons from the control toolbox toolbar, you can
group these by using the GroupName property of the option button. (Rightclick
on each optionbutton, choose properite

By default, each optionbutton has the same GroupName--the name of the
worksheet. This means that if you click on one optionbutton, then all the
others are turned off.

By giving each group a separate GroupName, you can have different groupings on
that worksheet.

And if you add a button (also from the Control toolbox toolbar) to that
worksheet, you can use code like:

Option Explicit
Private Sub CommandButton1_Click()
Dim OLEObj As OLEObject
For Each OLEObj In Me.OLEObjects
If TypeOf OLEObj.Object Is MSForms.OptionButton Then
OLEObj.Object.Value = False
End If
Next OLEObj
End Sub

Karen wrote:
>
> Hello, I'm using Office 2000 (Excel) and I'm trying to
> recreate a form from a hard copy. My computer version
> needs to look exactly the same. In this form there are
> circles that need to be filled in. I think I'm headed in
> the right direction by using the control toolbar and
> inserting option buttons since multiple circles may need
> to be filled in. I'm able to get all of them filled in
> but once they are filled in I can't get them to clear
> (toggle from filled in to not filled in). Any help or
> ideas for other ways to accomplish this task would be
> greatly appreciated.
>
> Karen

--

Dave Peterson

[karen]

Thanks for your reply. I must admit, your answer is
pretty much greek when speaking about "code". This is all
new to me. I had already figured out how to get
them "ungrouped". The portion I can't figure out is how
to toggle if it's filled in or empty. All of them start
out empty but once they are clicked to fill them in they
stay filled in. You can't click again to make them
empty. Will you please clarify your answer to this
beginner.

Thanks again.
Karen
>-----Original Message-----
>First, if you're using optionbuttons from the control
toolbox toolbar, you can
>group these by using the GroupName property of the option
button. (Rightclick
>on each optionbutton, choose properite
>
>By default, each optionbutton has the same GroupName--the
name of the
>worksheet. This means that if you click on one
optionbutton, then all the
>others are turned off.
>
>By giving each group a separate GroupName, you can have
different groupings on
>that worksheet.
>
>And if you add a button (also from the Control toolbox
toolbar) to that
>worksheet, you can use code like:
>
>Option Explicit
>Private Sub CommandButton1_Click()
> Dim OLEObj As OLEObject
> For Each OLEObj In Me.OLEObjects
> If TypeOf OLEObj.Object Is MSForms.OptionButton
Then
> OLEObj.Object.Value = False
> End If
> Next OLEObj
>End Sub
>
>Karen wrote:
>>
>> Hello, I'm using Office 2000 (Excel) and I'm trying to
>> recreate a form from a hard copy. My computer version
>> needs to look exactly the same. In this form there are
>> circles that need to be filled in. I think I'm headed in
>> the right direction by using the control toolbar and
>> inserting option buttons since multiple circles may need
>> to be filled in. I'm able to get all of them filled in
>> but once they are filled in I can't get them to clear
>> (toggle from filled in to not filled in). Any help or
>> ideas for other ways to accomplish this task would be
>> greatly appreciated.
>>
>> Karen
>
>--
>
>Dave Peterson
>.
>

[Dave Peterson]

I was suggesting that you add a commandbutton from that same control toolbox
toolbar.

After you put it on the worksheet, double click on it.

Paste that code into the code window that you now see.

Then back to excel. Click on the "design mode" icon on that same control
toolbox toolbar (so that excel thinks you're in the workbook doing real
work--not developing the form).

Then click on a few optionbuttons (just to fill them in). Then click that
button.

You can change the caption on that button, too:
"Click Me To Reset All The Option Buttons"

If you're worried about printing that commandbutton,
right click on it (while in design mode)
select Format control
Properties tab|uncheck that "print object" option.

karen wrote:
>
> Thanks for your reply. I must admit, your answer is
> pretty much greek when speaking about "code". This is all
> new to me. I had already figured out how to get
> them "ungrouped". The portion I can't figure out is how
> to toggle if it's filled in or empty. All of them start
> out empty but once they are clicked to fill them in they
> stay filled in. You can't click again to make them
> empty. Will you please clarify your answer to this
> beginner.
>
> Thanks again.
> Karen
> >-----Original Message-----
> >First, if you're using optionbuttons from the control
> toolbox toolbar, you can
> >group these by using the GroupName property of the option
> button. (Rightclick
> >on each optionbutton, choose properite
> >
> >By default, each optionbutton has the same GroupName--the
> name of the
> >worksheet. This means that if you click on one
> optionbutton, then all the
> >others are turned off.
> >
> >By giving each group a separate GroupName, you can have
> different groupings on
> >that worksheet.
> >
> >And if you add a button (also from the Control toolbox
> toolbar) to that
> >worksheet, you can use code like:
> >
> >Option Explicit
> >Private Sub CommandButton1_Click()
> > Dim OLEObj As OLEObject
> > For Each OLEObj In Me.OLEObjects
> > If TypeOf OLEObj.Object Is MSForms.OptionButton
> Then
> > OLEObj.Object.Value = False
> > End If
> > Next OLEObj
> >End Sub
> >
> >Karen wrote:
> >>
> >> Hello, I'm using Office 2000 (Excel) and I'm trying to
> >> recreate a form from a hard copy. My computer version
> >> needs to look exactly the same. In this form there are
> >> circles that need to be filled in. I think I'm headed in
> >> the right direction by using the control toolbar and
> >> inserting option buttons since multiple circles may need
> >> to be filled in. I'm able to get all of them filled in
> >> but once they are filled in I can't get them to clear
> >> (toggle from filled in to not filled in). Any help or
> >> ideas for other ways to accomplish this task would be
> >> greatly appreciated.
> >>
> >> Karen
> >
> >--
> >
> >Dave Peterson
> >.
> >

--

Dave Peterson

[Karen]

Thanks for the solution. It works great and saved me many
additional headaches. Keep up the good work. You have no
idea how appreciative I am.
>-----Original Message-----
>First, if you're using optionbuttons from the control
toolbox toolbar, you can
>group these by using the GroupName property of the option
button. (Rightclick
>on each optionbutton, choose properite
>
>By default, each optionbutton has the same GroupName--the
name of the
>worksheet. This means that if you click on one
optionbutton, then all the
>others are turned off.
>
>By giving each group a separate GroupName, you can have
different groupings on
>that worksheet.
>
>And if you add a button (also from the Control toolbox
toolbar) to that
>worksheet, you can use code like:
>
>Option Explicit
>Private Sub CommandButton1_Click()
> Dim OLEObj As OLEObject
> For Each OLEObj In Me.OLEObjects
> If TypeOf OLEObj.Object Is MSForms.OptionButton
Then
> OLEObj.Object.Value = False
> End If
> Next OLEObj
>End Sub
>
>Karen wrote:
>>
>> Hello, I'm using Office 2000 (Excel) and I'm trying to
>> recreate a form from a hard copy. My computer version
>> needs to look exactly the same. In this form there are
>> circles that need to be filled in. I think I'm headed in
>> the right direction by using the control toolbar and
>> inserting option buttons since multiple circles may need
>> to be filled in. I'm able to get all of them filled in
>> but once they are filled in I can't get them to clear
>> (toggle from filled in to not filled in). Any help or
>> ideas for other ways to accomplish this task would be
>> greatly appreciated.
>>
>> Karen
>
>--
>
>Dave Peterson
>.
>