Results 1 to 3 of 3

Thread: Excel Workbook ReOpening After Closing With Application.OnTime

  1. #1
    Junior Member
    Join Date
    Jul 2011
    Posts
    19
    Rep Power
    0

    Excel Workbook ReOpening After Closing With Application.OnTime

    Dear All ,
    I am using the below macro to close the workbook after crossing the time limit.
    In workbook, i pasted the below one

    Code:
    Private Sub Workbook_Open()
    Application.OnTime Now + TimeValue("00:03:00"), "close1_Me"
    End Sub
    
    and in the module,
    Sub close_me()
    ThisWorkbook.Close Savechanges:=True
    End Sub
    
    Sub close1_me()
        
        Dim objWsh          As Object, msg As String
        Const Delay         As Long = 3 'secs ?
        Const wButtons      As Long = 16 ' Boutons + icon
        
        Set objWsh = CreateObject("WScript.Shell")
        msg = "Please update the file. The file will close within 10 sec."
        
        objWsh.Popup msg, Delay, "Reminder !!!!!!!!", wButtons
        Application.OnTime Now + TimeValue("00:00:10"), "close_Me"
    
        Set objWsh = Nothing
    
    End Sub
    Now the problem is, if i am clossing before 3 mins (before time limit), it opens the file and asks for the clossure.
    The same repeats sometimes till i close all the workbook which is opened earlier.
    If it is clossing normally, there is no error or loop is appeared.

    Please help..

    Regards,
    Leo Paul
    Last edited by Excel Fox; 12-04-2012 at 11:54 AM. Reason: Code Tags

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Hi LeoPaulC,

    Please wrap your code using Code Tags. I have added the same for you this time.
    Try
    Code:
    Sub close_me()
            On Error Resume Next
            Application.OnTime Now(), "close1_Me", , False
            On Error GoTo 0
    ThisWorkbook.Close Savechanges:=True
    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

  3. #3
    Junior Member
    Join Date
    Jul 2011
    Posts
    19
    Rep Power
    0
    Dear Administrator,

    Thank you for the guidance which i am receiving every time

    regards,
    Leo Paul

Similar Threads

  1. Add VBA Reference From Another Application Excel To PowerPoint
    By ds1001 in forum Rajan Verma's Corner
    Replies: 1
    Last Post: 06-02-2013, 02:43 PM
  2. To Fin out Purchases against Closing stock
    By Prabhu in forum Excel Help
    Replies: 1
    Last Post: 05-15-2013, 09:48 AM
  3. Replies: 22
    Last Post: 03-19-2013, 07:57 AM
  4. Replies: 1
    Last Post: 02-14-2013, 11:08 AM
  5. Excel Application.OnKey With Parameter
    By Excel Fox in forum Excel Help
    Replies: 0
    Last Post: 11-29-2011, 01:31 PM

Posting Permissions

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