Message Box Appears Multiple Times

[quick_question]

I've read a variety of different threads, but my issue seems to be different from what I can see posted out there.

I have a macro built onto Sheet4 (renamed "Controls") that when the condition is met, calls a separate (Message Box) macro. My problem is that even when I select "OK" on the message box, it appears 3 more times before stopping to appear.

I think it has something to do with the Woeksheet_Calculate function, but I'm not well versed enough to understand why...

Macro #1:
Private Sub Worksheet_Calculate()
If Sheets("Controls").Range("$AS$22").Value = 1 Then
Call MsgBoxExclamationIcon
End If
End Sub

Calling Macro #2:
Sub MsgBoxExclamationIcon()
MsgBox "Warning: this series is no longer STANDARD and has an extended " _
& "lead time if available at all." & Chr(13) & Chr(10) & Chr(13) & Chr(10) _
& "NOW Standard Series Include:" & Chr(13) & Chr(10) & Chr(13) & Chr(10) _
& " SERIES" & vbTab & " STANDARD LENGTH" & Chr(13) & Chr(10) _
& " - 24A" & vbTab & "| 12ft" & Chr(13) & Chr(10) _
& " - 26A" & vbTab & "| 20ft" & Chr(13) & Chr(10) _
vbOKOnly + vbExclamation, "Series Warning"
End Sub

Any help is greatly appreciated.

 

[Eric W]

Try this:

Private Sub Worksheet_Calculate()
    If Sheets("Controls").Range("$AS$22").Value = 1 Then
        Application.EnableEvents = False
        Call MsgBoxExclamationIcon
        Application.EnableEvents = True
    End If
End Sub

You might also need to set a flag somewhere saying that the message box has been displayed. Otherwise, the event will trigger every time the sheet recalculates, and if the value is 1, even if it has not changed since last time, you'll call the Message box routine.

 

[Eric W]

This should show the message box only the first time that value turns to 1.

Public MsgFlag As Long

Private Sub Worksheet_Calculate()
    If Sheets("Controls").Range("$AS$22").Value = 1 Then
        If MsgFlag = 0 Then
            Call MsgBoxExclamationIcon
            MsgFlag = 1
        End If
    Else
        MsgFlag = 0
    End If
End Sub

 

[quick_question]

That works great, thanks.

One additional question - what if I want to call a different msgbox based on the value of a different cell on the same sheet (AS29)?

How would I craft this code to cycle through different conditions while keeping the msgbox from popping up more than once, and also allowing each to pop up regardless of if the other message box has already been displayed from a prior selection.

Keep in mind, these cells (AS22, AS29) are being impacted by slicer selections which could volley back and forth between the same or different selections.

Thanks again.

 

[Eric W]

Same idea, you'd just need a different flag for each cell you're monitoring:

Public MsgFlag22 As Long
Public MsgFlag29 As Long

Private Sub Worksheet_Calculate()
    
    If Sheets("Controls").Range("$AS$22").Value = 1 Then
        If MsgFlag22 = 0 Then
            Call MsgBoxExclamationIcon
            MsgFlag22 = 1
        End If
    Else
        MsgFlag22 = 0
    End If
    
    If Sheets("Controls").Range("$AS$29").Value = 1 Then
        If MsgFlag29 = 0 Then
            Call MsgBox2
            MsgFlag29 = 1
        End If
    Else
        MsgFlag29 = 0
    End If
    
End Sub

 

[quick_question]

Perfect, Thanks!