Warning Alerts Depending On Time Remaining To Finish Task

[peter renton]

Hi
I have attched a workbook,

When the time required for is entered in column J column k counts down the time left to complet the job, the cell goes red with one
hour to go.

is it possible to do the following:-

When the half hour left is reached a warning alert message box and or audio alert is shown, but if the completed box is selected in column P the alerts are disabled and the time frozen in K ???

Regards


Peter

[peter renton]

Hi

Has anybody any thoughts on this or prt of this?

[Excel Fox]

Change your formula as follows and drag down

K6=IF(W6="",IFERROR(V6-A$1,""),W6)

Change the number format of the cells from K6 to mm:ss

And replace your routine with this


Sub RunEveryMinute()
With Worksheets(1)
Range("A1").Value = Format(Now, "hh:mm:ss")
End With
If Range("P6").Value = "Completed" Then
Range("W6").Value = Range("K6").Value
End If
If Range("K6").Value < TimeValue("00:30:00") And Range("P6").Value <> "Completed" Then
MsgBox "Alert! Alert! Abort Mission!. This file will explode in " & Range("K6").Text & " minutes. Turn computer off if you want to live"
End If
Call OnTimeMacro
End Sub

[peter renton]

Hi

Thanks for looking at this, i have tried to copy this code but i cant seem to get it to work.
I was hoping that the the time in k6 would continue to count down the difference from the current time to the required time, and then show a warning at 1 hour then 1/2 hour but if the compled option was selected in p6 the time shown in k 6 would freeze to highlight when the job was completed.

Peter

[Excel Fox]

Except for the warning at 1 hour, the rest of it is exactly what I intended too. Did you call the macro that uses the OnTime method?

[peter renton]

Hi

Sorry still cant seem to get it to work? i have tried calling the macro etc, could you drop it into
the test sheet for me to see what i am doing wrong ??


Peter

[peter renton]

hI
I have added the coding

The warning comes up at 30 mins to go but re occurs every minute, how do i get it to do one warning at 60min to go then one reminder at 30mins to go and an over due warning when it goes minus? also could it pick up the customer name in the warning so its easy to see which row the alert is for?

Selecting the completed seem to stop the alert but not the clock (do i need to put a code in w column?) also code it restart the clock if this is de selected.

Sorry if i am been a bit thick here but i am just trying to get my head around how the codeing works

Thank you for looking

Peter

[peter renton]

Hi
I have manged to sort the picking up of customer name etc in the warning.

If someone has time to look at the other items that would be good

Peter

Also how do i get it to work on all rows in the sheet?

[Excel Fox]

Working on this. Will send you something tomorrow. Late here.

[peter renton]

Thank you for looking at it%D


Peter