VBA Clear data within range when a cell is changed. The cell is dependent on another cell on other sheet.

[PitaDodo]

Hi, i found this and applied:

Private Sub Worksheet_Activate()
If Range("S2") <> Range("A2").Value Then
Range("S2") = Range("A2").Value
Range("d2:g2").ClearContents
End If
End sub

Where S2 is the cell that depends on another cell on other sheet (which means when S2 changes, d2:g2 clears). By A2 i did "A2 always same like S2".

This works, however my range is now always cleared everytime i click on another sheet (not only when cell S2 changes).

I'm a beginner and would appreciate so much help. Thanks!

 

[Joe4]

Welcome to the Board!

That is because you used the "Worksheet_Activate" event, which fires EVERY time that sheet is activated.
If you only want it to run when cell S2 changes, then you need to use the "Worksheet_Change" event procedure, i.e.

Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit if cell S2 not updated
    If Intersect(Target, Range("S2")) Is Nothing Then Exit Sub
   
    If Range("S2") <> Range("A2").Value Then
        Application.EnableEvents = False
        Range("S2") = Range("A2").Value
        Range("D2:G2").ClearContents
        Application.EnableEvents = True
    End If
   
End Sub

 

[PitaDodo]

Welcome to the Board!

That is because you used the "Worksheet_Activate" event, which fires EVERY time that sheet is activated.
If you only want it to run when cell S2 changes, then you need to use the "Worksheet_Change" event procedure, i.e.

Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit if cell S2 not updated
    If Intersect(Target, Range("S2")) Is Nothing Then Exit Sub
  
    If Range("S2") <> Range("A2").Value Then
        Application.EnableEvents = False
        Range("S2") = Range("A2").Value
        Range("D2:G2").ClearContents
        Application.EnableEvents = True
    End If
  
End Sub

Hi, thanks for your response! However, yesterday I used the "Worksheet_Change", that is:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("S2")) Is Nothing Then
Range("D2:G2").ClearContents
End If
End Sub

But it doesn't work, as S2 depends on cell from another sheet.

If Range("S2") <> Range("A2").Value Then Application.EnableEvents = False Range("S2") = Range("A2").Value Range("D2:G2").ClearContents Application.EnableEvents = True

Now that we have extra codes here...will it work even though S2 depends on cell from another sheet? If yes, is it because the data in S2 is refreshed when we open the sheet where cell S2 is?

Thanks!

 

[Joe4]

Please answer ALL of the following questions.

What is the name of the sheet you are applying this to?
What is the exact formula that is in cell S2?
The cell that S2 is referencing, how is that being updated?

 

[PitaDodo]

Sheet I am applying: Sheet 1 (actually multiple sheets, but I guess one example is enough)

Formula in S2: =Sheet2!B1

B1 is actually month. When new month starts, B1 in sheet 2 is updated to a new month (eg Jan / feb/ etc) and the range in sheet 1 (and other sheets) is cleared.

Tq!

 

[Joe4]

So cell B1 on Sheet2 is then a formula also?
Something like:

=Month(TODAY())

That is going to make things a bit difficult, as Worksheet_Change procedure only run when a cell is manually updated, and it does not sound like any of these cells are being manually updated (it is all formulas).

There is another procedure called "Worksheet_Calculate", which is fired whenever any cell on the sheet is re-calculated. The caveat with that is it cannot tell which cell was re-calculated, only that some cell on the sheet was re-calculated. So it may run a lot more than you want it to.

I think we may be able to use the "Worksheet_Calculate" option if we also make use of some tracking cell. For example, when the code is fired it compares cell S2 to some other cell we are using for tracking, like cell AZ1, and if they are equal, do nothing. But if they are not, run your code, then copy the value to cell AZ1 for future comparisons.

So see if something like this works:

Private Sub Worksheet_Calculate()

'   If value in cell S2 matches value in cell AZ1, exit and do nothing
    If Range("S2").Value = Range("AZ1").Value Then Exit Sub
    
'   Check to see if value in S2 matches A2
    If Range("S2") <> Range("A2").Value Then
        Application.EnableEvents = False
        Range("S2") = Range("A2").Value
        Range("D2:G2").ClearContents
'       Copy value from S2 to AZ1 and save workbook
        Range("AZ1").Value = Range("S2").Value
        Application.EnableEvents = True
        ActiveWorkbook.Save
    End If
    
End Sub