Popup timer

josros60

Well-known Member
Joined
Jun 27, 2010
Messages
778
Office Version
  1. 365
Hi,

how can i make the codes below popup display reminder every 2 hour message.

Code:
Sub popup()Dim lstRow As Long
Dim i As Long
Dim msg As String
msg = "The following items are almost due" & vbCrLf & vbCrLf
lstRow = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To lstRow
    If Range("A" & i) - Date <= 3 Or Range("A" & i) - Date < 0 Then
     msg = msg & Range("B" & i).Value & "  " & "due in " & " " & Range("A" & i) - Date & " Days" & "  " & Format(Range("C" & i).Value, "$#,##0.00;($#,##0.00)") & vbCrLf
     End If
Next i
MsgBox msg
Call settimer
End Sub

Code:
Sub settimer()Application.OnTime Now + TimeValue("02:00:00"), "popup"
End Sub
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
What is your problem with the codes?
If you run the popup macro, you will instantly have the message, (if the condition is met). And at 2 hours it will run again.
If you first run the macro settimer, the first message appears in 2 hours (if the condition is met).

If the condition does not met, then it does not show a message.
 
Upvote 0
yes, it does every 2 hours. sorry Ignorant on no really good in VBA.

thanks so much for the clarification.
 
Upvote 0
Sorry,

question, how can the code be modify to popup message only if condition is met, keeps popping up every 2 hours even though no condition is met.

thanks again
 
Upvote 0
Try

Code:
Sub popup()
  Dim lstRow As Long, i As Long, msg As String
  msg = "The following items are almost due" & vbCrLf & vbCrLf
  lstRow = Range("A" & Rows.Count).End(xlUp).Row
  For i = 2 To lstRow
    If Range("A" & i) - Date <= 3 Or Range("A" & i) - Date < 0 Then
        msg = msg & Range("B" & i).Value & "  " & "due in " & " " & Range("A" & i) - Date & " Days" & "  " & Format(Range("C" & i).Value, "$#,##0.00;($#,##0.00)") & vbCrLf
        MsgBox msg
    End If
  Next i
  Call settimer
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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
Back
Top