How to show a Message Box when the value of a formula cell in an array is changed from 0 to 1

I have a spreadsheet table for data entry. When certain criteria on a line in the table is met, I want to trigger a Message Box as a reminder for the user to do something.

First, I wrote a formula in another column of the table which would return either a 1 or a 0 based on the data the user entered in that line of the table. This worked fine (FYI, for rows with no data entered yet, the formula returns a 0)

The part that is an issue is getting a message box to trigger when any of the cells in this new column turn from 0 to 1. I want the message to appear, the user reads it and clicks okay, and then the message box to go away and not appear again. I only want it to appear again when another line of data is entered which meets the criteria and turns that lines cell from a 0 to 1.

But when I tried to write some VBA code (See below) using a 'Private Sub Worksheet Calculate()' I quickly found that because the cell value remained 1 after I clicked ok on the message box, the message box was immediately triggered again, in a loop. Note, i wrote this code for 1 cell in mind, but what I really want is for this to apply to the whole 'W' column:

Private Sub Worksheet_Calculate()
 If Range("W1") = 1 Then
  MsgBox "Send email to author"
 End If
End Sub

I also then tried the following code based on someone elses post. This only ended up displaying the message every time I changed something on the worksheet, even if it didn't effect the value in column W. Again note, I really want it to apply to the whole of column W.

Private Sub Worksheet_Calculate()
   Dim target As Range
    Set target = Range("W1")
    If Not Intersect(Range("W1"), Range("W1")) Is Nothing Then
        MsgBox "Send email to author"
    End If
End Sub

I've gone as far as my understanding of VBA will take me, which is not far at all, so maybe someone can show me where I am going wrong.

Thanks.

Solution

So, the main point is that you need to save the previous state of W. One option is a global variable in VBA but that is not saved with the workbook, and any reset will delete its value. Therefore you should make some place for it e.g. in column X.

If W is the current state of 0/1 - shall we show a MsgBox, and X is the previous state, then you can use the below code:

Option Explicit

Private Sub Worksheet_Calculate()
    Worksheet_Calculate_ColW
End Sub

Private Sub Worksheet_Calculate_ColW()
    Dim wRange As Range: Set wRange = Application.Intersect(Me.Range("W:W"), Me.UsedRange)
    Dim xRange As Range: Set xRange = wRange.Offset(0, 1)
    Dim wValues() As Variant: wValues = wRange.Value
    Dim xValues() As Variant: xValues = xRange.Value
    Dim r As Long: For r = LBound(wValues, 1) To UBound(wValues, 1)
        If wValues(r, 1) <> xValues(r, 1) Then ' Changed
            If wValues(r, 1) = 1 Then          ' Changed to 1
                MsgBox "Send email to author: " & wRange.Cells(r, 1).Address & " has changed to 1"
            End If
            xRange.Cells(r, 1) = wValues(r, 1) ' Do not show up next time
        End If
    Next
End Sub

I have extracted this feature into function Worksheet_Calculate_ColW because Worksheet_Calculate may have other tasks as well. You may want to rename it to something more meaningful along with wRange, xRange and the other variables considering their semantic context.

Note: Although a cell in column X is changed by the function, which may cause a recalculation and a nested call to this function, the next time the values of X and W in the same row will be the same, therefore an endless loop is not a real threat.