Dont show message alert for 5 minutes

[ste33uka]

I use the following vba for a message alert,
once triggered , is it possible to make it not trigger again for 5 minutes?

Private Sub Worksheet_Alert()
Dim Msg As Long
    Dim myRange As Range
    Set myRange = Sheet85.Range("z26:z26")
    Dim cell As Range
    For Each cell In myRange
        Evaluate (cell)
        If StrComp(cell, "Yes", vbTextCompare) = 0 Then
            Msg = MsgBox("" & vbCrLf & " " & Range("h26").Value, vbExclamation)
     
        End If
    Next
               Set myRange = Sheet85.Range("z27:z27")
    For Each cell In myRange
        Evaluate (cell)
        If StrComp(cell, "Yes", vbTextCompare) = 0 Then
            Msg = MsgBox("" & vbCrLf & " " & Range("h27").Value, vbExclamation)
     
        End If
    Next
       
    End Sub

 

[ste33uka]

edit-
so if Z26 = yes ,
message would show, but then would not show again for 5 minutes
but alert for Z27 would still be active.

 

[GWteB]

Your code may be a derivative of an originally more elaborate procedure, but if not, I also have a suggestion for simplifying your code.
Furthermore, I would advise you to declare the msg variable as VbMsgBoxResult instead of an Integer.
You get the 5-minute delay by declaring the used variable statically and assigning a proper value.

Private Sub Worksheet_Alert()

    Static dtTime   As Date
    Dim Msg         As VbMsgBoxResult
    
    If Now > dtTime Then
        dtTime = Now + TimeSerial(0, 5, 0)
        If StrComp(Sheet85.Range("z26"), "Yes", vbTextCompare) = 0 Then
            Msg = MsgBox(vbCrLf & " " & Range("h26").Value, vbExclamation)
        End If
    End If
        
    If StrComp(Sheet85.Range("z27"), "Yes", vbTextCompare) = 0 Then
        Msg = MsgBox("" & vbCrLf & " " & Range("h27").Value, vbExclamation)
    End If
End Sub

 

[ste33uka]

Your code may be a derivative of an originally more elaborate procedure, but if not, I also have a suggestion for simplifying your code.
Furthermore, I would advise you to declare the msg variable as VbMsgBoxResult instead of an Integer.
You get the 5-minute delay by declaring the used variable statically and assigning a proper value.

Private Sub Worksheet_Alert()

    Static dtTime   As Date
    Dim Msg         As VbMsgBoxResult
   
    If Now > dtTime Then
        dtTime = Now + TimeSerial(0, 5, 0)
        If StrComp(Sheet85.Range("z26"), "Yes", vbTextCompare) = 0 Then
            Msg = MsgBox(vbCrLf & " " & Range("h26").Value, vbExclamation)
        End If
    End If
       
    If StrComp(Sheet85.Range("z27"), "Yes", vbTextCompare) = 0 Then
        Msg = MsgBox("" & vbCrLf & " " & Range("h27").Value, vbExclamation)
    End If
End Sub

i dont get any alert with that code for some reason

 

[GWteB]

Your alert depends on the values in Z26 and Z27, so try this to get the picture in connection with your wish of the 5 minute delay....

Sub RunMe()
    Dim i As Long
    For i = 1 To 4
        Call Worksheet_Alert_Example
    Next i
End Sub

Private Sub Worksheet_Alert_Example()

    Static dtTime   As Date
    Dim Msg         As VbMsgBoxResult

    If Now > dtTime Then
        dtTime = Now + TimeSerial(0, 5, 0)
        Msg = MsgBox("Only show this once every 5 minutes....")
    End If

    Msg = MsgBox("This is always shown")

End Sub

 

[ste33uka]

Your alert depends on the values in Z26 and Z27, so try this to get the picture in connection with your wish of the 5 minute delay....

Sub RunMe()
    Dim i As Long
    For i = 1 To 4
        Call Worksheet_Alert_Example
    Next i
End Sub

Private Sub Worksheet_Alert_Example()

    Static dtTime   As Date
    Dim Msg         As VbMsgBoxResult

    If Now > dtTime Then
        dtTime = Now + TimeSerial(0, 5, 0)
        Msg = MsgBox("Only show this once every 5 minutes....")
    End If

    Msg = MsgBox("This is always shown")

End Sub

sorry i dont understand any of that
i may to to rethink this

 

[ste33uka]

Your alert depends on the values in Z26 and Z27, so try this to get the picture in connection with your wish of the 5 minute delay....

Sub RunMe()
    Dim i As Long
    For i = 1 To 4
        Call Worksheet_Alert_Example
    Next i
End Sub

Private Sub Worksheet_Alert_Example()

    Static dtTime   As Date
    Dim Msg         As VbMsgBoxResult

    If Now > dtTime Then
        dtTime = Now + TimeSerial(0, 5, 0)
        Msg = MsgBox("Only show this once every 5 minutes....")
    End If

    Msg = MsgBox("This is always shown")

End Sub

instead of a time delay,
Could i change the following code so when alert message box is activated it would add 100 to to column X for row of alert ?
and message would dislay row for column H I J K

So example z32 = yes , message is displayed , for cells h32 i32 j32 k32 and cell x 32 would = 100 ?

Private Sub Worksheet_Alert()
Dim Msg As Long
    Dim myRange As Range
    Set myRange = Sheet85.Range("z1:z99")
    Dim cell As Range
    For Each cell In myRange
        Evaluate (cell)
        If StrComp(cell, "Yes", vbTextCompare) = 0 Then
            Msg = MsgBox("" & vbCrLf & " " & Range("h34").Value & vbCrLf & Range("i34").Value & vbCrLf & Range("j34") & Range("k34").Value & vbCrLf & Range("l34"), vbExclamation)
            
        End If
    Next
     

    End Sub

 

[GWteB]

sorry i dont understand any of that

Have you run the example procedures at all to see what happens (ie RunMe which calls the other example procedure four times)?

 

[ste33uka]

Have you run the example procedures at all to see what happens (ie RunMe which calls the other example procedure four times)?

i think im better off not adding a time frame now, could you help with post 7 here ?

 

[GWteB]

I would recommend starting a new thread and elaborating on your new requirements so we can help you better.