Click to Clear Radio Button

[dgold82]

I have a number of control groups on a worsheet with radio buttons in them.
If I locked the worksheet is there a way for a user to clear a radio button
by just clicking again on it without having to "right click" and click on
"clear contents". I'm sure there is a macro out there somewhere. Any help
would be appreciated.

 

[dgold82]

Any takers on this one? It would be helpful especially since I will be
disabling right-click for user of this spreadsheet. Thanks.

 

[Dave Peterson]

I'm guessing that you have a linked cell for that optionbutton and that linked
cell is locked.

If you unlock that cell, do things work ok?

If you have to have that cell locked, then instead of using it as the linked
cell, you could link to a different cell (on a different (hidden) worksheet) and
then use a formula to retrieve that value.

If this doesn't help, what type of optionbutton did you use--from the forms
toolbar or from the control toolbox toolbar.

 

[dgold82]

Thanks for responding Dave. The linked cell is actually unlocked and hidden.
My users wouldn't be able to click a radio button if the linked cell was
locked. My problem is that my users need to have some of the radio buttons
unclicked and if they accidentally click one they can't clear it (except by
clicking on reset all which was a command button that I made).

They need to be able to clear a radio button with having to rightclick.
Clicking on it again and having it clear would be the best since
rightclicking is disabled. Any code available that you know of?

 

[Dave Peterson]

It kind of sounds like you're using optionbuttons in place of checkboxes--just
as an indicator to choose something or not.

If that's the case, I'd replace the optionbuttons with checkboxes.

But if you wanted to keep the optionbuttons, then give the users another
option. Yes AND no (for each group).

 

[dgold82]

So I guess you are saying that there is no way to clear a radio button
without right clicking or resetting them all? I have it set up with radio
buttons because I am mimicking a scantron test for students to use. I would
use checkboxes but they don't look like the real thing. This is how it looks
for about 200 questions:

1. ( ) A ( ) B ( ) C ( ) D
2. ( ) F ( ) G ( ) H ( ) J
3. ( ) A ( ) B ( ) C ( ) D
etc

I don't want to add anything (if possible) to the format of the input for
simplicity purposes. Thank you for the suggestions though. Hopefully I'll
figure out how to code my way out of this.

 

[Dave Peterson]

So you've grouped the optionbuttons A to D, F to J, ....

Can you add a 5th optionbutton to each group for "none of the first 4"???

Or maybe you could provide another button that resets that group so that nothing
in that group is selected.

Any macro/button would depend on the type of button and optionbutton, too. You
didn't share where those optionbuttons (and buttons) came from.

So I guess you are saying that there is no way to clear a radio button
without right clicking or resetting them all? I have it set up with radio
buttons because I am mimicking a scantron test for students to use. I would
use checkboxes but they don't look like the real thing. This is how it looks
for about 200 questions:

1. ( ) A ( ) B ( ) C ( ) D
2. ( ) F ( ) G ( ) H ( ) J
3. ( ) A ( ) B ( ) C ( ) D
etc

I don't want to add anything (if possible) to the format of the input for
simplicity purposes. Thank you for the suggestions though. Hopefully I'll
figure out how to code my way out of this.

 

[dgold82]

Thanks again Dave. In theory I could add "none of the first 4", but like I
said below, my input mimicks their real test input and I would rather not
have that option for them VISIBLE (note the emphasis on visible).

I'm not sure I understand what you are asking for when you say that I didn't
share where the button comes from...I think you are suggesting that I create
another button somewhere to reset a group of radio buttons or add another
radio button that would be none of the first 4 or something. I could have
done that easily but my limitation is the look and style of the particular
scantron that I am trying to mimick.

The only other option I could think of is allowing "right click" so that
they could clear the contents if a student really wants to. Only problem with
that is that I will be disabling all command bars and right clicking with the
following code:

Sub Commands()
Dim oCB As CommandBar
For Each oCB In Application.CommandBars
oCB.Enabled = False
Next oCB
End Sub

The alternative would be allowing one exception and that would be to right
click and click on clear contents. I don't know how to edit the above code to
allow for that exception.

Thanks again for your patience and help with this.

 

[gmorris]

There might be a way to do it, but I could never find it. I've tried
everything I know to do it and that's just not the way OptionButtons
work. What I don't understand is, if one of the buttons IS to be
selected, why can't they just select another one and that clears the
currently selected button (hence the way OptionButtons work)?

 

[Dave Peterson]

I'm not sure why any workbook has to mimic the look and feel of that scantron (I
don't know what that is).

But if you don't like an additional optionbutton, you could put a button or
commandbutton near each group of optionbuttons that clear that group.

I would think that your commandbutton/button that clears all doesn't have the
same look and feel as the scantron.

What toolbar did you use to create the optionbuttons and what toolbar would you
use to create the commandbutton/button?

Thanks again Dave. In theory I could add "none of the first 4", but like I
said below, my input mimicks their real test input and I would rather not
have that option for them VISIBLE (note the emphasis on visible).

I'm not sure I understand what you are asking for when you say that I didn't
share where the button comes from...I think you are suggesting that I create
another button somewhere to reset a group of radio buttons or add another
radio button that would be none of the first 4 or something. I could have
done that easily but my limitation is the look and style of the particular
scantron that I am trying to mimick.

The only other option I could think of is allowing "right click" so that
they could clear the contents if a student really wants to. Only problem with
that is that I will be disabling all command bars and right clicking with the
following code:

Sub Commands()
Dim oCB As CommandBar
For Each oCB In Application.CommandBars
oCB.Enabled = False
Next oCB
End Sub

The alternative would be allowing one exception and that would be to right
click and click on clear contents. I don't know how to edit the above code to
allow for that exception.

Thanks again for your patience and help with this.

 

[dgold82]

Wow, I really appreciate the time you guys are taking to respond.
Unfortunatelly, I don't think you are understanding my previous posts and
that can be due to the fact that you aren't sitting here next to me with a
full understanding of this project. To answer Dave's questions:

1) a scantron is a sheet that a student fills out with their answers to
standardized tests. Like the ACT or SAT or GMAT etc. I want my worksheet to
look and feel like the same thing.
2) The command button and my radio buttons are from the "forms control"
toolbar not the activex controls. The "reset all" command button is up in a
designated area on the top of the worksheet that looks seperate from the
actual input. Looks like a toolbar that enables the user to jump around the
workbook.
3) Like a stated earlier, I know I could put a button next to the answers
that would clear a certain group box of buttons, but that would compromise
the look and I just don't want to do that. I'd rather just not have the
option for them at all until I figure this out.

I think gmorris understands where I am going with this. Looks like there are
a lot of people that have tried this to no avail. I just don't understand why
the radio buttons don't clear themselves if they are clicked again. I know it
would somewhat be like a checkbox...but there would still be some subtle
differences.

I think my final takaway from this VBA adventure is that I should either
just leave right clicking enabled or create a custom right click that only
would reset the radio button. Do you guys happen to know how to create a
custom right click (since I have disabled the official one using the above
VBA)? Thanks again for your time!

 

[Dave Peterson]

If you're using xl2003, look at:

Tools|Options|View tab
Look at how comments can be displayed.

tools|Options|International tab
Look at right to left section

Tools|Options|Calculation tab
look at the calculation section

You'll see how optionbuttons work.