Hello,

I’m trying to determine how to clear the option buttons on my worksheet for
the instance when the user first goes to the given page. I figure that if
they are blank, the user will realize that nothing has been selected so they
have to select something. I read the post “Set OptionBox Value via VBA”
posted earlier today and tried the using Chip’s suggestions in the
Workbook_Activate() procedure (thinking these would be “default” values”) but
kept getting various types of errors. I don’t even remember what anymore –
my head is spinning….

A little more info for you…

My workbook has two sheets, “Pre-Service” and “Post-Service.” The second
sheet contains six option buttons (added using the Control Toolbox) set up as
three groups of two buttons. Each group has a YES button and a NO button. I
haven’t linked the buttons to a particular cell via the properties, but I do
have code that will put a YES or NO in a given cell depending on which button
in the group was clicked. I’ve included the code below to help clarify what I
mean. I’m not sure it’s that efficient but it works.

Private Sub DesiccantYes_Click()
Worksheets("Post-Service").Range("N4") = "Yes"
End Sub
Private Sub DessicantNo_Click()
Worksheets("Post-Service").Range("N4") = "No"
End Sub
Private Sub OringYes_Click()
Worksheets("Post-Service").Range("N5") = "Yes"
End Sub
Private Sub OringNo_Click()
Worksheets("Post-Service").Range("N5") = "No"
End Sub
Private Sub TransducerYes_Click()
Worksheets("Post-Service").Range("N6") = "Yes"
End Sub
Private Sub TransducerNo_Click()
Worksheets("Post-Service").Range("N6") = "No"
End Sub

Thanks again!

Chris

Hi Chris,

You can just set all the option buttons to false with code. The following
code if placed in ThisWorkbook module will set them all to false when the
workbook is activated.

Not sure if you know this but just in case. The option button name and the
option button caption are 2 different properties. When you open properties,
the name is the first property and is used in code to identify the button.
The caption is simply what you see on the screen and while it defaults to the
button name when created, you do not use it to identify the button in code.

If you have changed the option button names from the default names then you
will need to edit the names in the following code.

Private Sub Workbook_Activate()

With Sheets("Post-Service")
.OptionButton1 = False
.OptionButton2 = False
.OptionButton3 = False
.OptionButton4 = False
.OptionButton5 = False
.OptionButton6 = False
End With

End Sub

--
Regards,

OssieMac


"CB" wrote:

> Hello,
>
> I’m trying to determine how to clear the option buttons on my worksheet for
> the instance when the user first goes to the given page. I figure that if
> they are blank, the user will realize that nothing has been selected so they
> have to select something. I read the post “Set OptionBox Value via VBA”
> posted earlier today and tried the using Chip’s suggestions in the
> Workbook_Activate() procedure (thinking these would be “default” values”) but
> kept getting various types of errors. I don’t even remember what anymore –
> my head is spinning….
>
> A little more info for you…
>
> My workbook has two sheets, “Pre-Service” and “Post-Service.” The second
> sheet contains six option buttons (added using the Control Toolbox) set up as
> three groups of two buttons. Each group has a YES button and a NO button. I
> haven’t linked the buttons to a particular cell via the properties, but I do
> have code that will put a YES or NO in a given cell depending on which button
> in the group was clicked. I’ve included the code below to help clarify what I
> mean. I’m not sure it’s that efficient but it works.
>
> Private Sub DesiccantYes_Click()
> Worksheets("Post-Service").Range("N4") = "Yes"
> End Sub
> Private Sub DessicantNo_Click()
> Worksheets("Post-Service").Range("N4") = "No"
> End Sub
> Private Sub OringYes_Click()
> Worksheets("Post-Service").Range("N5") = "Yes"
> End Sub
> Private Sub OringNo_Click()
> Worksheets("Post-Service").Range("N5") = "No"
> End Sub
> Private Sub TransducerYes_Click()
> Worksheets("Post-Service").Range("N6") = "Yes"
> End Sub
> Private Sub TransducerNo_Click()
> Worksheets("Post-Service").Range("N6") = "No"
> End Sub
>
> Thanks again!
>
> Chris
>

Hi OssieMac,

Thanks for taking the time to help. Your code worked like a charm.

At first I got a run-time error then I realized I had a rotten typo - the
same thing that gave me problems when I tried Chip's suggestions. Sheesh! I
should stay away from this machine when I'm tired. I ended up wasting several
hours.

Anyway, thanks again!

Chris

"OssieMac" wrote:

> Hi Chris,
>
> You can just set all the option buttons to false with code. The following
> code if placed in ThisWorkbook module will set them all to false when the
> workbook is activated.
>
> Not sure if you know this but just in case. The option button name and the
> option button caption are 2 different properties. When you open properties,
> the name is the first property and is used in code to identify the button.
> The caption is simply what you see on the screen and while it defaults to the
> button name when created, you do not use it to identify the button in code.
>
> If you have changed the option button names from the default names then you
> will need to edit the names in the following code.
>
> Private Sub Workbook_Activate()
>
> With Sheets("Post-Service")
> .OptionButton1 = False
> .OptionButton2 = False
> .OptionButton3 = False
> .OptionButton4 = False
> .OptionButton5 = False
> .OptionButton6 = False
> End With
>
> End Sub
>
> --
> Regards,
>
> OssieMac
>
>
> "CB" wrote:
>
> > Hello,
> >
> > I’m trying to determine how to clear the option buttons on my worksheet for
> > the instance when the user first goes to the given page. I figure that if
> > they are blank, the user will realize that nothing has been selected so they
> > have to select something. I read the post “Set OptionBox Value via VBA”
> > posted earlier today and tried the using Chip’s suggestions in the
> > Workbook_Activate() procedure (thinking these would be “default” values”) but
> > kept getting various types of errors. I don’t even remember what anymore –
> > my head is spinning….
> >
> > A little more info for you…
> >
> > My workbook has two sheets, “Pre-Service” and “Post-Service.” The second
> > sheet contains six option buttons (added using the Control Toolbox) set up as
> > three groups of two buttons. Each group has a YES button and a NO button. I
> > haven’t linked the buttons to a particular cell via the properties, but I do
> > have code that will put a YES or NO in a given cell depending on which button
> > in the group was clicked. I’ve included the code below to help clarify what I
> > mean. I’m not sure it’s that efficient but it works.
> >
> > Private Sub DesiccantYes_Click()
> > Worksheets("Post-Service").Range("N4") = "Yes"
> > End Sub
> > Private Sub DessicantNo_Click()
> > Worksheets("Post-Service").Range("N4") = "No"
> > End Sub
> > Private Sub OringYes_Click()
> > Worksheets("Post-Service").Range("N5") = "Yes"
> > End Sub
> > Private Sub OringNo_Click()
> > Worksheets("Post-Service").Range("N5") = "No"
> > End Sub
> > Private Sub TransducerYes_Click()
> > Worksheets("Post-Service").Range("N6") = "Yes"
> > End Sub
> > Private Sub TransducerNo_Click()
> > Worksheets("Post-Service").Range("N6") = "No"
> > End Sub
> >
> > Thanks again!
> >
> > Chris
> >