How to fire worksheet_calculate event when data in a specific cell in a different sheet, is changed?

0
votes

I created a worksheet_calculate event macro to return a message box (CHANGE DETECTED!) whenever the value in the cells W4656:W4657 change. These values are referenced from another sheet in the same workbook.

My problem is the worksheet_calculate event is fired whenever data is entered anywhere in the workbook.

Could this be modified such that the worksheet_calculate event is fired only when data in a specific cell (a cell in a different sheet) is changed.

Private Sub Worksheet_Calculate()
Dim Xrg As Range
Set Xrg = Range("W4656:W4657")
If Not Intersect(Xrg, Range("W4656:W4657")) Is Nothing Then
    MsgBox ("CHANGE DETECTED!!")
    ActiveWorkbook.Save
End If
End Sub
3
excelvba
Calculate doesn't work like that - there is no Target parameter. Perhaps you could check the values of those two cells or use the change event for the source sheet. – SJR
You have a volatile function in that worksheet. Look up volatile function. – user4039065
You could use the Change event and point the Target to wherever These values are referenced from another sheet in the same workbook, and if any of these values change then fire the event... – Xabier

3 Answers

0
votes

Well, if we examine these lines of your code

Dim Xrg As Range
Set Xrg = Range("W4656:W4657")
If Not Intersect(Xrg, Range("W4656:W4657")) Is Nothing Then

Since we set Xrg, then immediately use it, we can rewrite that as

If Not Intersect(Range("W4656:W4657"), Range("W4656:W4657")) Is Nothing Then

which will always be true. So, every time the worksheet Calculates, it will say "CHANGE DETECTED!"

Ideally, you want to store the values in those Cells somewhere, and then just run a comparison between the cells and the stored values. Using Worksheet Variables, you could get the following: (You could also store the values in hidden worksheet as an alternative)

Option Explicit 'This line should almost ALWAYS be at the start of your code modules

Private StoredW4656 As Variant 'Worksheet Variable 1
Private StoredW4657 As Variant 'Worksheet Variable 2

Private Sub Worksheet_Calculate()
    On Error GoTo SaveVars 'In case the Variables are "dropped"

    'If the values haven't changed, do nothing
    If (Me.Range("W4656").Value = StoredW4656) And _
        (Me.Range("W4657").Value = StoredW4657) Then Exit Sub

    MsgBox "CHANGE DETECTED!", vbInformation
SaveVars:
    StoredW4656 = Me.Range("W4656").Value
    StoredW4657 = Me.Range("W4657").Value
End Sub
0
votes

So I've managed to find a solution (work around?) to my problem. I ended up using a macro to check if the the number in Sheet 38, Cell W4656 which was referenced from Sheet 5, Cell J2, has changed. If yes, fire a macro. If not, do nothing. I've realized that with the code below, worksheet_calculate event is fired only when there is change in Sheet 5, Cell J2 or Sheet 38, Cell W4656 which is what I want.

Private Sub Worksheet_Calculate()
Static OldVal As Variant
 If Range("w6").Value <> 24 Then
  MsgBox ("XX")
'Call Macro
End If
End Sub
0
votes

I've updated my code and made it cleaner, and shamelessly stole some of Chronocidal's approach (my original code required the workbook to be closed and opened to work). So here is what Sheet5 looks like in my example:

Sheet5

And here is Sheet38. In my example I simply setup formulas in Sheet38!W4656:W4657 to equal Sheet5!$J$2 ... so when Sheet5!$J$2 changes so does Sheet38!W4656:W4657 which will trigger the code.

Sheet38

And copy this code into ThisWorkbook ...

Option Explicit

Dim vCheck1 As Variant
Dim vCheck2 As Variant

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If vCheck1 <> Sheet38.Range("W4656") Or vCheck2 <> Sheet38.Range("W4657") Then
        MsgBox ("CHANGE DETECTED!!")
        Application.DisplayAlerts = False
        ActiveWorkbook.Save
        Application.DisplayAlerts = True
        vCheck1 = Sheet38.Range("W4656")
        vCheck2 = Sheet38.Range("W4657")
    End If
End Sub

Like this ...

Workbook