Clear cell content based on another cell calculation

[olivier5b]

Hello,

I have a cell A1 in sheet2 linked cell A1 in sheet1 (simply A1='sheet1'!A1). A1 in sheet1 is a data validation drop down menu.

I want to clear the content of A2 in sheet 2 everytime the content of A1 in sheet2 changes/is updated. That is everytime the value of A1 in sheet1 is changed using the drop down menu.

I tried using a Worksheet_Change event macro (which I do not fully understand) but it won't work with a cell that updates from a calculation. It also doesn't work if triggered from a cell from another worksheet (I tried linking it to cell A1 on sheet1 in this case).

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
Range("A2").ClearContents
End Sub

Can you think of a simple solution to clear the content of cell A2 in sheet2 when A1 in sheet2 updates?

Thanks for your help.

 

[JoeMo]

This goes into Sheet1 module:

Private Sub Worksheet_Change(ByVal Target As Range)
Set Target = Target.Cells(1, 1)
If Not Intersect(Target, Range("A1")) Is Nothing Then
    With Sheets("Sheet2")
        .Range("A2").ClearContents
    End With
End If
End Sub

To install the code:
1. Right-click the worksheet you want to apply it to and choose 'View Code'. This will open the VBE window.
2. Copy the code above from your browser window and paste it into the white space in the VBE window.
3. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
4. Make sure you have enabled macros whenever you open the file or the code will not run.

 

[Jeffrey20]

This in the Worksheet 1 module should work...

Private Sub Worksheet_Change(ByVal Target As Range)

If Target(1, 1) = Range("A1") Then
Worksheets(2).Range("A2").ClearContents
End If


End Sub

 

[olivier5b]

Thanks JoeMoe amd Jeffrey20, both solutions work beautifully. I appreciate the time you took to answer. Merry Christmas!

 

[RUSTYC]

Hello,

This is my first post and I've been working on this for a little bit now and wanted to reach out for help. I am having the same issue as olivier5b did, set up is the same. I tried both codes, but unsure why it isn't working. Im not getting back any errors or anything either. The only thing different is the cell locations which I have changed.

My goal is when G3 is changed in Sheet1 (G3 is a data validation list) I would like E3 in Sheet2 cleared of the contents. Here are both codes I have tried (in the Sheet1 VBE):

Private Sub Worksheet_Change(ByVal Target As Range)
Set Target = Target.Cells(1, 1)
If Not Intersect(Target, Range("G3")) Is Nothing Then
With Sheets("Sheet9")
.Range("E3").ClearContents
End With
End If
End Sub
--------------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)

If Target(1, 1) = Range("G3") Then
Worksheets(9).Range("E3").ClearContents
End If

End Sub

Thanks in advance!

 

[JoeMo]

Hello,

This is my first post and I've been working on this for a little bit now and wanted to reach out for help. I am having the same issue as olivier5b did, set up is the same. I tried both codes, but unsure why it isn't working. Im not getting back any errors or anything either. The only thing different is the cell locations which I have changed.

My goal is when G3 is changed in Sheet1 (G3 is a data validation list) I would like E3 in Sheet2 cleared of the contents. Here are both codes I have tried (in the Sheet1 VBE):

Private Sub Worksheet_Change(ByVal Target As Range)
Set Target = Target.Cells(1, 1)
If Not Intersect(Target, Range("G3")) Is Nothing Then
With Sheets("Sheet9")
.Range("E3").ClearContents
End With
End If
End Sub
--------------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)

If Target(1, 1) = Range("G3") Then
Worksheets(9).Range("E3").ClearContents
End If

End Sub

Thanks in advance!

You say you want to clear E3 in Sheet2, but neither of the scripts you posted does that. The first one clears E3 in Sheet9 and the second clears E3 in the ninth sheet from the left on the tab selection.

 

[RUSTYC]

You say you want to clear E3 in Sheet2, but neither of the scripts you posted does that. The first one clears E3 in Sheet9 and the second clears E3 in the ninth sheet from the left on the tab selection.

Hi JoeMo,
Thanks for the reply, I meant Sheet 9. I would like E3 in Sheet 9 to clear.
Thanks!

 

[JoeMo]

Hi JoeMo,
Thanks for the reply, I meant Sheet 9. I would like E3 in Sheet 9 to clear.
Thanks!

Then your first macro (in post #5) should work. If it doesn't either you have not installed it correctly (see post #2 for installation instructions), or you do not have events enabled. To enable events follow these steps:
1. Right-click the sheet tab and choose 'View Code' to open the VBE window.
2. Press ctrl & g keys to open the Immediate Window.
3. In the Immediate Window type "Application.EnableEvents = True" (w/o the quote marks) and press Enter
4. Now change G3 in sheet1 and see if your macro responds.