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

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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,487
Messages
6,113,937
Members
448,534
Latest member
benefuexx

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