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

bobmc

Board Regular
Joined
Mar 13, 2002
Messages
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.
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,140
Members
448,551
Latest member
Sienna de Souza

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