Excel - creating reminder pop up based on criteriaClosed

Question

Hello, 
may you please help me in creating  a reminder to pop up for every 5 hours once for a cell range having text equal to "pending"
I was able to create msg box and got stuck.

TrowaD · Answer

Hi 123gh,

For that we place the following code at the appropriate sheet:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1:A10")) Is Nothing Then Exit Sub

If Target.Value = "Pending" Then
    Call RunOnTime
End If
End Sub

Here you can adjust the cell range on the second row and the trigger word on the forth.

Then place the code below in a standard module:
Dim dTime As Date

Sub RunOnTime()
dTime = Now + TimeSerial(5, 0, 0)
Application.OnTime dTime, "RunOnTime"

MsgBox "Put your reminder text here."

End Sub

Sub CancelOnTime()
Application.OnTime dTime, "RunOnTime", , False
End Sub

Here you can adjust the time schedule on the forth row (5 hours, 0 minutes, 0 seconds).

When you don't want to be reminded any more, run the macro called CancelOnTime.

Best regards,
Trowa

123gh · Answer

Hi Trowa,

Thanks much,,I tried this is popping the msg even when the text pending is not there in the selected cell range.
I have a cell range colums A to AF ,and only if this cell range =pending it shuld show reminder,later after completion,the same is updated to completion so it shuld stop giving reminder.

TrowaD · Answer

Hi 123gh,

The code will only run when "Pending" is found in the specified range. Did you run the "RunOnTime" macro manually?

You say your range is columns A:AF. Does that mean that one can put "Pending" in B10, G132 and AB45 for example. Then if B10 is changed to "Completed" the reminder will stop while G132 and AB45 are still awaiting completion while the reminder has already stopped.

If you only have one "Pending" at a time replace the worksheet code with:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("A:AF")) Is Nothing Then Exit Sub

If Target.Value = "Pending" Then Call RunOnTime
If Target.Value = "Completed" Then Call CancelOnTime

End Sub
The reminder will stop when "Completed" is found in the given range, so it doesn't have to be the same cell.

Best regards,
Trowa

Excel - creating reminder pop up based on criteria

3 responses

Similar discussions

Subscribe To Our Newsletter!