Dont show message alert for 5 minutes

ste33uka

Active Member
Joined
Jan 31, 2020
Messages
430
Office Version
  1. 365
Platform
  1. Windows
I use the following vba for a message alert,
once triggered , is it possible to make it not trigger again for 5 minutes?

VBA Code:
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
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

ste33uka

Active Member
Joined
Jan 31, 2020
Messages
430
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Sep 10, 2010
Messages
2,147
Office Version
  1. 2013
Platform
  1. Windows
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.

VBA Code:
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

Active Member
Joined
Jan 31, 2020
Messages
430
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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

Well-known Member
Joined
Sep 10, 2010
Messages
2,147
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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....

VBA Code:
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

Active Member
Joined
Jan 31, 2020
Messages
430
Office Version
  1. 365
Platform
  1. Windows
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....

VBA Code:
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
 
Last edited:

ste33uka

Active Member
Joined
Jan 31, 2020
Messages
430
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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....

VBA Code:
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 ?
VBA Code:
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

Well-known Member
Joined
Sep 10, 2010
Messages
2,147
Office Version
  1. 2013
Platform
  1. Windows
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

Active Member
Joined
Jan 31, 2020
Messages
430
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Sep 10, 2010
Messages
2,147
Office Version
  1. 2013
Platform
  1. Windows
I would recommend starting a new thread and elaborating on your new requirements so we can help you better.
 

Forum statistics

Threads
1,144,389
Messages
5,724,073
Members
422,534
Latest member
ThiccNugg

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top