Results 1 to 10 of 10

Thread: Warning Alerts Depending On Time Remaining To Finish Task

  1. #1
    Member
    Join Date
    May 2013
    Posts
    84
    Rep Power
    12

    Warning Alerts Depending On Time Remaining To Finish Task

    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
    Attached Files Attached Files

  2. #2
    Member
    Join Date
    May 2013
    Posts
    84
    Rep Power
    12
    Hi

    Has anybody any thoughts on this or prt of this?

  3. #3
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    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

    Code:
    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
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  4. #4
    Member
    Join Date
    May 2013
    Posts
    84
    Rep Power
    12
    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

  5. #5
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    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?
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  6. #6
    Member
    Join Date
    May 2013
    Posts
    84
    Rep Power
    12
    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

  7. #7
    Member
    Join Date
    May 2013
    Posts
    84
    Rep Power
    12
    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
    Attached Files Attached Files

  8. #8
    Member
    Join Date
    May 2013
    Posts
    84
    Rep Power
    12
    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?
    Last edited by peter renton; 06-18-2013 at 08:36 PM. Reason: added query

  9. #9
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Working on this. Will send you something tomorrow. Late here.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  10. #10
    Member
    Join Date
    May 2013
    Posts
    84
    Rep Power
    12
    Thank you for looking at it


    Peter

Similar Threads

  1. Replies: 6
    Last Post: 06-06-2013, 03:17 AM
  2. Calculate Time Difference Between Time In HH:MM
    By Stalker in forum Excel Help
    Replies: 8
    Last Post: 03-28-2013, 03:27 PM
  3. Displayin Date/Time in "original" time zone
    By Rasm in forum Excel Help
    Replies: 4
    Last Post: 04-21-2012, 02:02 AM
  4. Replies: 2
    Last Post: 12-12-2011, 01:51 PM
  5. Spreading a time range (shift time, etc) in columns.
    By Rajesh Kr Joshi in forum Excel Help
    Replies: 1
    Last Post: 08-23-2011, 11:45 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •