Reminders pop up based on time input in another cell

[IKHAN]

Hi,

Is there a VBA code to pop up reminder message 15 mins before time provided in another cell and update original time after time lapse.

Have attached Test file with details.

 

[Chihiro]

While it is possible, it's not ideal to do this type of operation/coding in Excel Macro, you have to be careful to stop the code at some point and also this only works if user is not logged out.

I'd recommend using your email client for reminder and pop-up.

See link for detailed example.
http://www.globaliconnect.com/excel...rvals-or-a-specified-time&catid=79&Itemid=475

 

[IKHAN]

Thanks Chihiro for prompt reply.

Understand It's ideal to use email client, However for our work nv,email client isn't feasible, Coz worksheet gets passed on to other person after his\her shift is done.

So macro will assist user to intput date for last email sent and countdown should start based on current computer time and typed date and time.

 

[Chihiro]

Here's very basic code.

Sub StartTime() is assigned to the button to initiate the process.

eProcess is inserted into Workbook_BeforeClose to terminate Application.Ontime process before workbook is closed (otherwise, it will continue to run and may force open the workbook).

At set time, it will beep and pop-up will open, hitting OK will set next reminder time.
Hit Cancel and it will stop timer and record Date&Time in H2 as last email sent.

Sub StartTime()

Cells(2, 2) = Format(Time, "hh:mm AM/PM")
Cells(2, 8) = Format(Time + Cells(2, 6) / 24 - 15 / 24 / 60, "hh:mm AM/PM")
Call sProcess
End Sub

Sub sProcess()
Dim dTime As Date
Dim Ret_Type As Integer

dTime = Now + TimeValue("00:00:05")
Application.OnTime dTime, "sProcess", , True

If Time >= Cells(2, 8) Then
    Beep
    Ret_Type = MsgBox("Hit OK to set next Reminder, Cancel to stop Macro", vbOKCancel)
    Select Case Ret_Type
    Case 1
        Cells(2, 8) = Format(Time + Cells(2, 6) / 24 - 15 / 24 / 60, "hh:mm AM/PM")
    Case 2
        eProcess
        Cells(2, 4) = Now
    End Select
End If
End Sub

Sub eProcess()
Application.OnTime dTime, "sProcess", , False
End Sub

Edit: Woops, was missing a line in code.

 

[IKHAN]

Thank you very much Chihiro for the code , It works perfectly for us.