Can I make a group of cells act like radio buttons?

bobmc

Board Regular
Joined
Mar 13, 2002
Messages
142
I'd like to program three cells so that if data is entered into any one of them, the other two become blank.

Any help?
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Bobmc -
Try something like:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$1" And
Target.Formula<> "" Then [A2,A3].ClearContents
If Target.Address = "$A$2" And Target.Formula<> "" Then [A1,A3].ClearContents
If Target.Address = "$A$3" And Target.Formula<> "" Then [A1,A2].ClearContents
End Sub

Right-click your worksheet tab, select "View Code" and put the code there.
Tom
This message was edited by Tom Morales on 2002-03-14 12:05
 
Upvote 0
Thanks for the reply!!!

It came close to working...

However, when I tab out of a given cell after entering anything <>"", the other cell that already has an entry doesn't 'CLEARCONTENTS' until I tab back into the cell I've just entered a value in.
Also, the bottom-most of the three cells in question doesn't respond well...it wants to override entries into cells above it.

Any more info on this is appreciated!
 
Upvote 0
You could try a slight variation on Tom's code as below: -

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then [A2,A3].ClearContents
If Target.Address = "$A$2" Then [A1,A3].ClearContents
If Target.Address = "$A$3" Then [A1,A2].ClearContents
End Sub
 
Upvote 0
Again, close...

This time, the top two of the three cells in question don't keep an entry. It erases itself as soon as I enter data.

The bottom of the three cells holds its entry. But when I enter into one of the two cells above it, then hit tab, the bottom one doesn't go away and there's still no entry above.

Hmmm.
 
Upvote 0
I'm confused now. You asked for code that would blank out two cells from a group of three, if an entry is made into the other cell. Mine does that, and I'm presuming Tom's does too. Could you reiterate exactly what you want?
 
Upvote 0
OK...I'M GETTING CLOSER!!!

Here's the deal...

Instead of the code header being SELECTION_CHANGE, I made it simply CHANGE. That solves the problem of entries not sticking. Now, here's the last little bug...
If there's an entry in the bottom of the three cells, and I put an entry into the middle cell, the bottom cell keeps its entry rather than disappearing. Everything else works!

Any last suggestions?

Thanks.
 
Upvote 0
Hmmm. My code works fine for me, whenever an entry is made in any of the cells the other two are blanked regardless of where the entry is made. Also, it already is using the Change event rather than SelectionChange. Copy and paste the code I gave you above into your worksheet and try that.
 
Upvote 0
MY BAD...

Found a letter that was off. Sorry for the confusion...and...

THANK YOU FOR YOUR WISDOM!! IT WORKS GREAT!
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top