Delete the contents of a cell if (REVISED)

[mrlanier]

If have simplified a problem I stated earlier:

If B1=1, delete the contents of A1. If B1 changes to any other value,
I do not want the former contents of A1 to return. Instead, I want to
be able to manually enter new contents, knowing that as often as B1
returns to equal 1, anything in A1 will be deleted. As I admit to my
novice status, the simpler the solution, the better. Thanks.

Michael

 

[merjet]

Add to worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$1" And Target.Value = 1 Then Range("A1") =
""
'if A1 is changed and already B1=1, then A1 won't be deleted.
'you may want to enable the following line.
'If Target.Address = "$A$1" Then Range("B1") = ""
End Sub

Hth,
Merjet

 

[mrlanier]

Thanks Gary's Student and merjet for your suggestions. I'll be trying
both shortly and let you know shortly.

Michael

 

[mrlanier]

Gary's Student,

I have encountered 2 problems:

First, B1 has to equal something, so instead of nothing, can we
reformulate to make B1=0?

Second, I'm getting the message "Ambiguous name detected:
Worksheet_Change". does this have anything to do with the fact that
I have a different macro on the same worksheet labeled "Private Sub
Worksheet_Change(ByVal TargetCell As Range)", which is slightly
different from yours (you don't have "Cell" shown?

Michael

 

[mrlanier]

Gary's Student,

I have encountered 2 problems:

First, B1 has to equal something, so instead of nothing, can we
reformulate to make B1=0?

Second, I'm getting the message "Ambiguous name detected:
Worksheet_Change". does this have anything to do with the fact that
I have a different macro on the same worksheet labeled "Private Sub
Worksheet_Change(ByVal TargetCell As Range)", which is slightly
different from yours (you don't have "Cell" shown?

Michael

 

[merjet]

First, B1 has to equal something, so instead of nothing, can we

reformulate to make B1=0?

Yes, e.g.:
If Target.Address = "$A$1" Then Range("B1") = 0

Second, I'm getting the message "Ambiguous name detected:
Worksheet_Change". does this have anything to do with the fact that
I have a different macro on the same worksheet labeled "Private Sub
Worksheet_Change(ByVal TargetCell As Range)", which is slightly
different from yours (you don't have "Cell" shown?

Yes. You need to combine them, which is probably not a problem if
they have different "if conditions". Repace "TargetCell" with "Target"
in code before or after combining.

Hth,
Merjet

 

[mrlanier]

Thanks merjet,

Unfortunately, when I make the suggested change, other existing
conditional macros no longer work. Thanks for trying, but this seems
to be beyond me for the time being.

Michael

 

[Don Guillett]

Post ALL of your code here for comments and suggestions.