Excel - creating reminder pop up based on criteria

Closed
123gh
Posts
2
Registration date
Monday June 23, 2014
Status
Member
Last seen
June 24, 2014
- Jun 23, 2014 at 07:52 AM
TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
- Jun 24, 2014 at 11:02 AM
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.

3 replies

TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
514
Jun 23, 2014 at 11:53 AM
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
0
123gh
Posts
2
Registration date
Monday June 23, 2014
Status
Member
Last seen
June 24, 2014

Jun 24, 2014 at 01:41 AM
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.
0
TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
514
Jun 24, 2014 at 11:02 AM
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
0