[SOLVED] Send Pop-Up Alert when the value in a cell on a non-active sheet <> 0

[ignatius108]

Hello,

I am new to the forum and look forward to learning from all of you.

I have a work file used by a lot of people and the data needs to reconcile on a final sheet. I already have the formula built in that cell which should stay at zero, but I find the cell does not always reconcile to zero when I open the file because the people using the file forget to check it still ties out, so then we don't know who accidentally messed it up so we can coach them, and more importantly what they did that caused the error so we can fix it.

I would like to build an alert where regardless of the sheet that is active, if the cell on the non-active sheet no longer is zero (whether less than zero or greater than zero), a pop-up immediately alerts the user that the value on that cell has changed based on their last action.

Here is what I have so far which I got offline using ChatGPT and then tried to modify, but I am running into two problems:

1. This only works when the sheet is active (I have been posting this code to the code for the particular sheet, so I am hoping there is some way within ThisWorkbook that the code would work for the cell I want instead even if the sheet is not active).

I tried to work around this issue by having a reference on each of the other sheets to the sheet and cell that should be zero, then using the same code as above for every sheet. For whatever reason, the code does not run on the sheets when I link it like this.

2. Anytime the cell changes even if going back to zero, there is a pop-up message. Clearly I am not doing something right as it seems to notice when the formula in that cell runs rather than monitoring when the amount has changed from zero.


Thank you in advance for your insight.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim numdependences As Integer
On Error Resume Next
HasDependents = Target.Dependents.Count
If Err = 0 Then
    If InStr(Target.Dependents.Address, "$D$3") <> 0 Then
        MsgBox "You have initiated a calculation change on cell D3 of worksheet sheet1 with your last action.  Please check that cell"
    End If
End If
On Error GoTo 0
End Sub