How can I made a marco run every hour and a half?
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: How can I made a marco run every hour and a half?

  1. #1
    Guest

    Default

     
    I need to run it every 90 mins. Thanks

  2. #2
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi

    Try

    Sub StartOff()
    Application.OnTime Now + TimeValue("00:90:00"), "DoIt"
    End Sub

    Sub DoIt()
    Run "StartOff"
    'Your Code
    End Sub


  3. #3
    Guest

    Default

    I get an error on sub startoff

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Brisbane, Down Under
    Posts
    542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Make a small change as follows:

    Public ThisTime As Double

    Sub StartOff()
    ThisTime = Now + TimeValue("00:90:00")
    Application.OnTime earliesttime:=ThisTime, procedure:="DoIt"

    Sub DoIt()
    Run "StartOff"
    'Your Code
    End Sub


  5. #5
    Guest

    Default

    I am still getting an error BTW I am using Excel 97 if that matters

  6. #6
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-07 16:07, Anonymous wrote:
    I am still getting an error BTW I am using Excel 97 if that matters
    I think you just need to change the order of the sub named DoIt (see below).

    Sub DoIt()
    'Your Code
    Run "StartOff"
    End Sub

    Regards,


    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Brisbane, Down Under
    Posts
    542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    silly me - you need to change
    ThisTime = Now + TimeValue("00:90:00") to
    ThisTime = Now + TimeValue("01:30:00") as you as time is in hh:mm:ss or you will get adata type mismatch (also forgot the end sub before sub DoIt())

  8. #8
    Guest

    Default

    Sam S that works! thanks

  9. #9
    Guest

    Default

    OK guys the file runs the Marco at the set interval, I changed it to 1 minute to check it, now how do I cut it OFF, when I close the file it will OPEN BACK UP BY ITS SELF! And start running the Marco. thanks

  10. #10
    Board Regular
    Join Date
    Feb 2002
    Location
    Stockton, California
    Posts
    281
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    I had this similar problem. I have some code that someone on this board helped me with (I'm sorry, i dont remember who ) shuts the worksheet down after it hasnt been used for a certain time, and eventhough the sheets were being closed, after the set time, they opened and closed again.

    The problem youre having is that the code is still being run eventhough the file is closed. To stop this, you need to disable the counter. Im not the best person to tell you exactly how to do that but, heres the code I use:

    In "ThisWorkbook" I have this code:

    'This disables the time coutner and resets it when a caculation is made
    Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    Call Disable
    Call SetTime
    End Sub

    'This disables the time counter and resets it when a sheet is changed
    Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
    Call Disable
    Call SetTime
    End Sub

    and in a module, i have this code:


    Dim DownTime As Date

    'This is the code that the macros in ThisWorkbook calls to set the timer
    Sub SetTime()
    DownTime = Now + TimeValue("00:15:00")
    Application.OnTime DownTime, "ShutDown"
    End Sub

    'This is the code that the macros in ThisWorkbook calls to close the file
    Sub ShutDown()
    ActiveWorkbook.Save
    ActiveWorkbook.Close

    End Sub

    'This is the code that the macros in ThisWorkbook calls to disable the timer
    Sub Disable()
    On Error Resume Next
    Application.OnTime EarliestTime:=DownTime, Procedure:="ShutDown", Schedule:=False
    End Sub



    Im assuming youll need something similar to the Disable code.

    Hope this helps some

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com