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

bobmc · Mar 14th, 2002, 11:40 AM

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?

Tom Morales · Mar 14th, 2002, 01:04 PM

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 ]

bobmc · Mar 14th, 2002, 01:20 PM

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!

Mudface · Mar 14th, 2002, 01:26 PM

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

bobmc · Mar 14th, 2002, 01:37 PM

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.

Mudface · Mar 14th, 2002, 01:51 PM

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?

bobmc · Mar 14th, 2002, 01:54 PM

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.

Mudface · Mar 14th, 2002, 02:00 PM

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.

bobmc · Mar 14th, 2002, 02:05 PM

MY BAD...

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

THANK YOU FOR YOUR WISDOM!! IT WORKS GREAT!

Mudface · Mar 14th, 2002, 02:07 PM

Hehe, phew

. Good luck with the rest

.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 14th, 2002, 11:40 AM	#1
bobmc Board Regular Join Date: Mar 2002 Posts: 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?

Mar 14th, 2002, 01:04 PM	#2
Tom Morales Board Regular Join Date: Feb 2002 Location: Richland, Washington Posts: 91	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 ]

Mar 14th, 2002, 01:20 PM	#3
bobmc Board Regular Join Date: Mar 2002 Posts: 142	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!

Mar 14th, 2002, 01:26 PM	#4
Mudface MrExcel MVP Join Date: Feb 2002 Location: Sunny, spring-like Hull Posts: 3,339	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

Mar 14th, 2002, 01:37 PM	#5
bobmc Board Regular Join Date: Mar 2002 Posts: 142	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.

Mar 14th, 2002, 01:51 PM	#6
Mudface MrExcel MVP Join Date: Feb 2002 Location: Sunny, spring-like Hull Posts: 3,339	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?

Mar 14th, 2002, 01:54 PM	#7
bobmc Board Regular Join Date: Mar 2002 Posts: 142	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.

Mar 14th, 2002, 02:00 PM	#8
Mudface MrExcel MVP Join Date: Feb 2002 Location: Sunny, spring-like Hull Posts: 3,339	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.

Mar 14th, 2002, 02:05 PM	#9
bobmc Board Regular Join Date: Mar 2002 Posts: 142	MY BAD... Found a letter that was off. Sorry for the confusion...and... THANK YOU FOR YOUR WISDOM!! IT WORKS GREAT!

Mar 14th, 2002, 02:07 PM	#10
Mudface MrExcel MVP Join Date: Feb 2002 Location: Sunny, spring-like Hull Posts: 3,339	Hehe, phew . Good luck with the rest .