Msgbox show only once

Question

Can someone modify my code so that the message box will only show once? I've been at it for a while now without succes.

The file I'm working on is a register. Whenever the sum of AD13:AJ13 exceed 4 the msg should show, Prompting action from the user. However as of now, once a cell within the range have exceeded 4 the msgbox shows on all changes within the range, even sums below 4. That's is not intended, i only want the user to be notified once for each cell within the range (AD13:AJ13)

Code:

Private Sub Worksheet_Calculate()

    Dim myCell As Range

    For Each myCell In Range("AD13:AJ13")
        If myCell > 4 Then
        MsgBox "Management approval is required once the value exceed 4"
            Exit Sub
        End If
    Next myCell
End Sub

Answer 1

I kind of agree with the comments below the question as Worksheet_Change seems like a more natural trigger. However, the problem could be that this range is not changed directly (i.e. cells have formulae which could depend on cells from other sheets or even other workbooks).

You basically need to somehow save the current state of these cells. Please try this code and see if it helps or opens up a new window of ideas for you.

Private Sub Worksheet_Calculate()
    Dim rngSavedState As Range
    Dim j As Integer
    Dim bMsgBoxShown As Boolean
    
    Set rngSavedState = Range("AD14:AJ14")
    
    Application.EnableEvents = False
    With Range("AD13:AJ13")
        bMsgBoxShown = False
        For j = 1 To .Columns.Count
            If .Cells(1, j).Value <> rngSavedState.Cells(1, j).Value Then
                rngSavedState.Cells(1, j).Value = .Cells(1, j).Value
                If .Cells(1, j) > 4 And Not bMsgBoxShown Then
                    MsgBox "Management approval is required once the value exceed 4"
                    bMsgBoxShown = True
                End If
            End If
        Next j
    End With
    Application.EnableEvents = True
End Sub

You obviously need to change the address of rngSaveState to suit your application. All the best