Control Macro With Times

  • all


    I am looking for code that will start a macro by counting in seconds prior to an event according to it's due time.
    Cell Value A4, A12 A20,( more could be added, depending on how many workers are present at the office at the time)


    So if worker A is due to start a task at 1.00 pm, then 45 seconds prior to that event the worker can be notified.
    ( Cell Values A4 and D4 )


    Then there are multiple tasks to be performed and each task is reminded in seconds after or before the set times the events ( Act) is due to start.
    The code would keep checking every 1 minute for a new list of events between the hours of 9-5 (Variable)


    I have provided a demo showing how I would enter my times and what macro is to be activated.



    Thanks

  • Re: Control Macro With Times


    Quote from Dave Hawley

    No idea what you mean by "then 45 seconds prior to that event the worker can be notified"


    Try the OnTime Method


    I think the OP is after a way of scheduling something or other. The time that this has to occur is known. The work to which that (task??) has been assigned internally and is being notified that he has to start that task.


    A couple of questions though.


    Is it really required that they start these tasks "on the dot" as it were ?? If not I would be tempted to use a worklist where the next job is added to the bottom of the list and the job to be serviced is taken from the top of the list.


    Is this a spreadsheet whereby all the users have to have this sheet open to work, or is this a standalone spreadsheet used by one person alone ? If this is a multi-user set up then really the best answer is find another way because excel and multi-users don't mix (there are plenty of threads on this and the reason why)


    Lastly, what happens if the user does not have excel open at the time he is due to start the task ?


    Regards


    Rich

  • Re: Control Macro With Times


    Quote

    Is it really required that they start these tasks "on the dot"


    Yes, it does not mean they start on the dot, they get notified by way of email activated at that point in time.
    All I am asking is how do I write a VBA timer to Activate another macro by counting in seconds ?


    Quote

    Is this a spreadsheet whereby all the users have to have this sheet open to work, or is this a standalone spreadsheet used by one person alone ?


    No, this is a standalone and I am the Administrator of my project.

    Quote

    Lastly, what happens if the user does not have excel open at the time he is due to start the task ?


    As mentioned above, they get notified by e-mail.
    It could be an egg-boil timer, it does not matter what I use it for, does it ?

  • Re: Control Macro With Times


    Dave,
    I have looked at the codes you suggest and do use these codes in other areas of the same workbook,
    but where it says

    Code
    Private Sub Workbook_Open()
         Application.OnTime TimeValue("15:00:00"), "MyMacro"
    End Sub


    the part where it say's :

    Code
    TimeValue("15:00:00")

    must be the cell reference, because the times change constantly and these values is best suited to be copy and pasted to the cell, not to go into the VBA code.



    Thanks

  • Re: Control Macro With Times


    So, I would assume it might look something like this for the first part.


    Code
    Application.OnTime TimeValue = range("A1"), "MyMacro"


    If value of A1 is the start time of a task, and I want a macro firing at Value of A2 which might quote -45 in seconds from value of A1.
    So if A1 = 15:00 and A2 = -45, then macro would activate at 14:59:15
    How would this be coded ?
    Thanks.

  • Re: Control Macro With Times


    You probably can't just subtract A2 from A1 since A1 is a full time and A2 is seconds. A1-A2 effectively is subtracting 45 days.


    You could use something like this though:

    Code
    =A1-TIME(0,0,45)
  • Re: Control Macro With Times


    Quote from PTG258

    It could be an egg-boil timer, it does not matter what I use it for, does it ?


    Actually it does.


    For example if you were thinking about using a mutli user spreadsheet then you would have been told that you are starting out on an impossible task.


    Secondly, people here may have a better idea of doing what you want to do.


    Thirdly, what happens if you didn't realise the implications of a user being off line ?


    So, whilst they may seem like dozy questions to you, it helps to give other people here an idea of what you're up to and propose more fitting solutions.


    Regards


    Rich



    Oh.


    Ps. If you want something as mission critical as an egg-timer, I wouldn't use Microsoft.

  • Re: Control Macro With Times


    PTG258, please understand that this forum is all about sharing knowledge. If a complete stranger is willing to help you in their free time, the least you can do is answer their questions. In addition, as pointed out above by Rich, many others will read this Thread so it will help many 1000's more if you are cooperative.

  • Re: Control Macro With Times


    Oh.


    Ps. If you want something as mission critical as an egg-timer, I wouldn't use Microsoft.


    hahahahahahaaaaa
    Don't make me laugh about Microsoft and it's crappy OS.
    Let's see now, Win 3.11, 95/98 and just last week I had BSoD on XP and lost all my work. THAT WHY I AM HERE ! because of a 3rd class egg beater of a operating system that keeps crashing due to registry issues and whatever else.
    Don't push it buddy, I am here because of Microsoft and the delays it has caused me.




    Linux Rocks and you cannot deny it.

  • Re: Control Macro With Times


    PTG258,


    You seem to be making the case for more disclosure not less. The operating system crashed because of your previous coding?


    Jim

  • Re: Control Macro With Times


    No it did not crash because of previous VBA coding.


    In fact everything I had learnt from here was ready to go.
    Then registry lost it's marbles for some reason after a security update re-boot.
    The technician said it was HD. Replaced the HD but that made the main EXE program inoperable because the programmer who made that program is no longer supportive of their own product.
    But I can access the files of the crashed HD, but the programs will not work.
    To fix registry and the files to see if it will work again, 3 PC shops said they cannot be bothered and another wants $150.00 per hour and may take upto 3 hours.
    Now, I have to re-create the entire exe program that used feed excel with data.
    The difference is, it will be VBA doing the exact same task, only slower.


    Do I have to explain what the exe program is/was or, does/did ?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!