Type into one cell and automatically clear contents in another cell
 

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.

Type into one cell and automatically clear contents in another cell
 

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" wrote in message
m...
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.

Type into one cell and automatically clear contents in another cell
 

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


"Bo" wrote in message
m...
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.

Type into one cell and automatically clear contents in another cell
 

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


Thank you for your help....


"Bill Barclift" wrote in message . ..
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" wrote in message
m...
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

Type into one cell and automatically clear contents in another cell
 

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


"Bo" wrote in message
om...
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


Thank you for your help....


"Bill Barclift" wrote in message
. ..
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" wrote in message
m...
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