Popup timer

[josros60]

Hi,

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

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

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

 

[DanteAmor]

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.

 

[josros60]

yes, it does every 2 hours. sorry Ignorant on no really good in VBA.

thanks so much for the clarification.

 

[DanteAmor]

Youre welcome & thanks for the feedback.

 

[josros60]

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

 

[DanteAmor]

Try

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

 

[josros60]

Thanks so much, worked perfectly.


much appreciated all your help.

 

[DanteAmor]

Again with pleasure. Thanks for the feedback