+ Reply to Thread
Results 1 to 5 of 5

Timer Macro Causes WB To Save While Closed

Hybrid View

  1. #1
    Registered User
    Join Date
    12-05-2023
    Location
    USA
    MS-Off Ver
    MS Office 365
    Posts
    5

    Timer Macro Causes WB To Save While Closed

    I have a macro that starts a timer when the workbook is first opened. When the timer hits "0", the workbook saves and closes. Works fine. I also have it where when the workbook is "Read-Only", the timer does not trigger the "save and close" of the macro. If I have another instance of excel open, and close the macro enabled WB, the macro still runs, even though the WB I have open now has no macros in it.

    It will still run the "save and close" function of the original WB macro.

    Is there anything I can add to it to make it specific to that WB with the timer macro? Even when it's closed without saving, it still runs the save if there is another excel sheet open.

    Any help would be greatly appreciated!

    Thank you in advance!

    Dim ResetTime As Date
    
    Sub StartTimer()
        ResetTime = Now + TimeValue("00:00:30") ' Set initial timer for 2 minutes
        Application.OnTime ResetTime, "CloseWorkbook" ' Schedule closing the workbook at the specified time
    End Sub
    
    Sub ResetTimer()
        On Error Resume Next
        Application.OnTime ResetTime, "CloseWorkbook", , False ' Cancel previously scheduled close event
        ResetTime = Now + TimeValue("00:00:30") ' Reset timer for another 2 minutes
        Application.OnTime ResetTime, "CloseWorkbook" ' Schedule closing the workbook again
    End Sub
    
    Sub Workbook_Open()
        StartTimer ' Start the timer when the workbook is opened
    End Sub
    
    Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
        ResetTimer ' Reset the timer when any activity is detected
    End Sub
    Public Sub StartTimer()
        If Not TimerStarted Then
            Application.OnTime Now + TimeValue("00:00:30"), "CloseWorkbook"
                TimerStarted = True
            End If
    End Sub
    Public Sub CloseWorkbook()
        If Now > LastActivity + TimeValue("00:00:30") Then
        On Error Resume Next
        If Not ThisWorkbook.ReadOnly Then
            ThisWorkbook.Save
        End If
        If Not ThisWorkbook.ReadOnly Then
            ThisWorkbook.Close
        End If
    SaveChanges = False
        Else
            StartTimer
        End If
    End Sub
    Attached Files Attached Files
    Last edited by vettaforza; 12-06-2023 at 12:15 PM.

  2. #2
    Registered User
    Join Date
    12-05-2023
    Location
    USA
    MS-Off Ver
    MS Office 365
    Posts
    5

    Re: Workbook Saves After Closing with Other Workbooks Open

    If it helps, I did try to put "Option Explicit" at the top of the macro, that didn't seem to do anything.

  3. #3
    Registered User
    Join Date
    12-05-2023
    Location
    USA
    MS-Off Ver
    MS Office 365
    Posts
    5

    Re: Workbook Saves After Closing with Other Workbooks Open

    Any help would be appreciated!

  4. #4
    Registered User
    Join Date
    10-16-2023
    Location
    Poland
    MS-Off Ver
    Excel 2019 32bit WIN10
    Posts
    99

    Re: Timer Macro Causes WB To Save While Closed

    Add this macro to the ThisWorkbook module:
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
        On Error Resume Next
        Application.OnTime ResetTime, "CloseWorkbook", , False 
    End Sub

  5. #5
    Registered User
    Join Date
    12-05-2023
    Location
    USA
    MS-Off Ver
    MS Office 365
    Posts
    5

    Re: Timer Macro Causes WB To Save While Closed

    Quote Originally Posted by Tajan View Post
    Add this macro to the ThisWorkbook module:
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
        On Error Resume Next
        Application.OnTime ResetTime, "CloseWorkbook", , False 
    End Sub
    Thank you, that worked!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Close workbook if autosave is off
    By Tonyhansson in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 08-14-2021, 03:23 AM
  2. [SOLVED] Autosave by timer
    By DD44 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-10-2017, 10:54 AM
  3. Autosave and close error
    By krishnaa_kumarr88 in forum Excel General
    Replies: 0
    Last Post: 11-03-2014, 10:47 AM
  4. Autosave/close gone horribly wrong - where to place code?
    By Greed in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-08-2012, 10:07 AM
  5. AutoSave on Close excel 2007
    By CrazyHorse in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-26-2011, 12:10 PM
  6. AutoSave before close
    By mattsvai in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-14-2006, 11:16 AM
  7. autosave before close
    By J_J in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-31-2005, 11:06 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1