Type into one cell and automatically clear contents in another cell

  • Thread starter Thread starter Bo
  • Start date Start date
B

Bo

Is there a way to type information into a cell and have visual basic
clear the contents in another cell without clicking on a button etc?

cell A1 cell B1
..595 .42 (.42 would be deleted by typing .595 in cell A1)

Or the contents in cell A1 would be deleted if Cell B1 value changes
or is greater than 0.
 
Try something like this using the Change event of the Worksheet object:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.AddressLocal = Range("A1").AddressLocal Then
Range("B1").ClearContents
End If
End Sub


Bill Barclift
 
Bo

try something along these lines:

' Sheet1 Class Module

Private Sub Worksheet_Change(ByVal Target As Range)
' specific check on cells A1 and B1
If Intersect(Target, Range("A1:B1")) Is Nothing Then Exit Sub
Application.EnableEvents = False
Select Case Target.Address(False, False)
Case "A1": Range("B1").Value = ""
Case "B1": Range("A1").Value = ""
End Select
Application.EnableEvents = True
End Sub

Or ...

Private Sub Worksheet_Change(ByVal Target As Range)
' check columns A and B
If Intersect(Target, Range("A:B")) Is Nothing Then Exit Sub
Application.EnableEvents = False
Select Case Left(Target.Address(False, False), 1)
Case "A": Target.Offset(0, 1).Value = ""
Case "B": Target.Offset(0, -1).Value = ""
End Select
Application.EnableEvents = True
End Sub

You might need to check for specifics like greater than 0

Regards

Trevor
 
Bill:
Thank you for the response to my question. The code works very good
but has a minor problem. If cell a1 changes it will delete the
contents in cell b1 which works fine. But when I add code for b1
(typing in a value will delete the contents in cell a1) is where the
problem comes in. It works but when cell a1 contents is deleted it
activates the code again and clears the contents in cell b1. Typing in
either cell will completely delete information in both cells. Hope you
have a fix for this.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.AddressLocal = Range("A1").AddressLocal Then
Range("B1").ClearContents
End If
If Target.AddressLocal = Range("b1").AddressLocal Then
Range("a1").ClearContents
End If
End Sub
Click to expand...


Thank you for your help....
 
Bo

You need:

Application.EnableEvents = False
'your code
Application.EnableEvents = True


why not try the solutions I posted on the 26/09/03:


try something along these lines:

' Sheet1 Class Module

Private Sub Worksheet_Change(ByVal Target As Range)
' specific check on cells A1 and B1
If Intersect(Target, Range("A1:B1")) Is Nothing Then Exit Sub
Application.EnableEvents = False
Select Case Target.Address(False, False)
Case "A1": Range("B1").Value = ""
Case "B1": Range("A1").Value = ""
End Select
Application.EnableEvents = True
End Sub

Or ...

Private Sub Worksheet_Change(ByVal Target As Range)
' check columns A and B
If Intersect(Target, Range("A:B")) Is Nothing Then Exit Sub
Application.EnableEvents = False
Select Case Left(Target.Address(False, False), 1)
Case "A": Target.Offset(0, 1).Value = ""
Case "B": Target.Offset(0, -1).Value = ""
End Select
Application.EnableEvents = True
End Sub

You might need to check for specifics like greater than 0

Regards

Trevor
 
Back
Top