Toggling cell entries...ONE LAST QUESTION...

bobmc · Mar 15th, 2002, 10:45 AM

Yesterday, Tom Morales and Mudface were kind enough (and smart enough) to help me develop a toggling set of three cells, where entry into one cell automatically blanked the other two. Here's the code...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$AD$133" Then [AD134,AD135].ClearContents
If Target.Address = "$AD$134" Then [AD133,AD135].ClearContents
If Target.Address = "$AD$135" Then [AD133,AD134].ClearContents
End Sub

HERE'S MY PROBLEM...

The above works perfectly. I now have two other cells that I would like to have do the same thing, just with eachother...(not with the three above.) I tried this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$AD$133" Then [AD134,AD135].ClearContents
If Target.Address = "$AD$134" Then [AD133,AD135].ClearContents
If Target.Address = "$AD$135" Then [AD133,AD134].ClearContents
If Target.Address = "$AD$63" Then [AD64].ClearContents
If Target.Address = "$AD$64" Then [AD63].ClearContents
End Sub

Basically I just used the same code for cells AD63 & AD64. The problem lies in that when I try to enter data into either of these cells, it immediately disappears, with both cells remaining empty.

Any ideas on this?

Thanks.

smozgur · Mar 15th, 2002, 10:54 AM

try this one.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$AD$133" Then [AD134,AD135].ClearContents
If Target.Address = "$AD$134" Then [AD133,AD135].ClearContents
If Target.Address = "$AD$135" Then [AD133,AD134].ClearContents
If Target.Address = "$AD$63" Then [AD64,AD64].ClearContents
If Target.Address = "$AD$64" Then [AD63,AD63].ClearContents

End Sub

suat

smozgur · Mar 15th, 2002, 11:01 AM

Because if you use [AD64].ClearContents then Change event runs again and this time the target address is just AD64 which force [AD63].ClearContents. But if you use [AD64,AD64].ClearContents then target address would be AD64,AD64 which isnot equal to AD64. Just a little cheat.

suat

bobmc · Mar 15th, 2002, 11:32 AM

Hey, smozgur...

You're brilliant!

Thanks mucho.

bobmc.

smozgur · Mar 16th, 2002, 08:37 PM

August · Aug 3rd, 2005, 05:29 AM

This would be great for my needs
Is there a set up that would allow only 2 cells of a 3 cell range to contain values.

ie in a range a1,b1,c1 value is entered in a1 then in b1 and then in c1, when the 3rd value is entered the value in the first cell is cleared out, and so on.

Hope this makes sense

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

Mar 15th, 2002, 10:45 AM	#1
bobmc Board Regular Join Date: Mar 2002 Posts: 142	Yesterday, Tom Morales and Mudface were kind enough (and smart enough) to help me develop a toggling set of three cells, where entry into one cell automatically blanked the other two. Here's the code... Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$AD$133" Then [AD134,AD135].ClearContents If Target.Address = "$AD$134" Then [AD133,AD135].ClearContents If Target.Address = "$AD$135" Then [AD133,AD134].ClearContents End Sub HERE'S MY PROBLEM... The above works perfectly. I now have two other cells that I would like to have do the same thing, just with eachother...(not with the three above.) I tried this: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$AD$133" Then [AD134,AD135].ClearContents If Target.Address = "$AD$134" Then [AD133,AD135].ClearContents If Target.Address = "$AD$135" Then [AD133,AD134].ClearContents If Target.Address = "$AD$63" Then [AD64].ClearContents If Target.Address = "$AD$64" Then [AD63].ClearContents End Sub Basically I just used the same code for cells AD63 & AD64. The problem lies in that when I try to enter data into either of these cells, it immediately disappears, with both cells remaining empty. Any ideas on this? Thanks.

Mar 15th, 2002, 10:54 AM	#2
smozgur BatCoder Join Date: Feb 2002 Location: Turkey Posts: 764	try this one. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$AD$133" Then [AD134,AD135].ClearContents If Target.Address = "$AD$134" Then [AD133,AD135].ClearContents If Target.Address = "$AD$135" Then [AD133,AD134].ClearContents If Target.Address = "$AD$63" Then [AD64,AD64].ClearContents If Target.Address = "$AD$64" Then [AD63,AD63].ClearContents End Sub suat

Mar 15th, 2002, 11:01 AM	#3
smozgur BatCoder Join Date: Feb 2002 Location: Turkey Posts: 764	Because if you use [AD64].ClearContents then Change event runs again and this time the target address is just AD64 which force [AD63].ClearContents. But if you use [AD64,AD64].ClearContents then target address would be AD64,AD64 which isnot equal to AD64. Just a little cheat. suat

Mar 15th, 2002, 11:32 AM	#4
bobmc Board Regular Join Date: Mar 2002 Posts: 142	Hey, smozgur... You're brilliant! Thanks mucho. bobmc.

Mar 16th, 2002, 08:37 PM	#5
smozgur BatCoder Join Date: Feb 2002 Location: Turkey Posts: 764

Aug 3rd, 2005, 05:29 AM	#6
August Board Regular Join Date: Jun 2004 Posts: 220	This would be great for my needs Is there a set up that would allow only 2 cells of a 3 cell range to contain values. ie in a range a1,b1,c1 value is entered in a1 then in b1 and then in c1, when the 3rd value is entered the value in the first cell is cleared out, and so on. Hope this makes sense