jcaldararo
jcaldararo

Reputation: 29

Macro in Excel to save every 30 minutes and close after 35 minutes of no use. I need to unload ThisWorkbook, but can't figure out how

VBA code for autosaving and for closing the workbook if idle is working. The problem is, Excel continues to run the code if another instance of the program was open when the code closed the workbook. I think what I need to do is to unload the workbook, but I can't figure out how. I've tried "Unload Workbook," "Unload ThisWorkbook," and "Unload ResetTimer" [the module which detects activity and starts the 35 minute timer over]. I'm getting an error that Workbook/ThisWorkbook/ResetTimer are not object that can be unloaded. I can't find a list of what objects can be unloaded.

Here is the code under ThisWorkbook

Option Explicit

Private Sub ThisWorkbook_Open()
    If ThisWorkbook.ReadOnly = False Then
    Application.OnTime Now + TimeValue("00:30:00"), "SaveThis"
    End If

    If ThisWorkbook.ReadOnly = False Then
    Application.OnTime Now + TimeValue("00:35:00"), "CloseDownFile"
    End If
End Sub

Private Sub ThisWorkbook_Close()  
    Unload ThisWorkbook
'    Unload ResetTimer
End Sub

Private Sub ThisWorkbook_SheetCalculate(ByVal Sh As Object)
ResetTimer
End Sub

Private Sub ThisWorkbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
ResetTimer
End Sub

Private Sub ThisWorkbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
ResetTimer
End Sub

Here is the Module:

Public CloseDownTime As Variant

Public Sub ResetTimer()
On Error Resume Next
If Not IsEmpty(CloseDownTime) Then Application.OnTime EarliestTime:=CloseDownTime, Procedure:="CloseDownFile", Schedule:=False
CloseDownTime = Now + TimeValue("00:35:00") ' change as needed
Application.OnTime CloseDownTime, "CloseDownFile"
End Sub

Public Sub CloseDownFile()
On Error Resume Next
ThisWorkbook.Close SaveChanges:=True
Unload ThisWorkbook
End Sub

Sub SaveThis()
    Application.DisplayAlerts = False
    ThisWorkbook.Save
    Application.DisplayAlerts = True

Application.OnTime Now + TimeValue("00:30:00"), "SaveThis"
End Sub

Upvotes: 0

Views: 619

Answers (2)

jcaldararo
jcaldararo

Reputation: 29

Here is the corrected code to save every 30 minutes and to close after 35 minutes of no use. Thank you to @TimWilliams for all of the help!

Code under ThisWorkbook:

Option Explicit

Private Sub Workbook_Open()
Call StartTimers
End Sub

Private Sub Workbook_Close()
CancelTimers
End Sub

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
CancelCloseTimer
StartCloseTimer
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
CancelCloseTimer
StartCloseTimer
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
CancelCloseTimer
StartCloseTimer
End Sub

Code for Module:

Option Explicit

Public CloseTime As Variant
Public SaveTime As Variant

Public Sub StartTimers()
    StartSaveTimer
    StartCloseTimer
End Sub

Public Sub CancelTimers()
    CancelSaveTimer
    CancelCloseTimer
End Sub

Sub StartSaveTimer()
    If ThisWorkbook.ReadOnly Then Exit Sub
    CancelSaveTimer 'remove any existing timer
    SaveTime = Now + TimeValue("00:30:00") 'save frequency, change as needed
    Application.OnTime SaveTime, "SaveThis"
End Sub

Sub CancelSaveTimer()
    On Error Resume Next
    Application.OnTime EarliestTime:=SaveTime, Procedure:="SaveThis", Schedule:=False
    On Error GoTo 0
End Sub

Sub StartCloseTimer()
    If ThisWorkbook.ReadOnly Then Exit Sub
    CancelCloseTimer 'remove any existing timer
    CloseTime = Now + TimeValue("00:35:00") 'idle time before closing, change as needed
    Application.OnTime CloseTime, "CloseThis"
End Sub

Sub CancelCloseTimer()
    On Error Resume Next
    Application.OnTime EarliestTime:=CloseTime, Procedure:="CloseThis", Schedule:=False
    On Error GoTo 0
End Sub

Public Sub CloseThis()
    On Error Resume Next
    CancelTimers
    ThisWorkbook.Close SaveChanges:=True
End Sub

Sub SaveThis()
    Application.DisplayAlerts = False
    ThisWorkbook.Save
    Application.DisplayAlerts = True
    StartSaveTimer
End Sub

Upvotes: 1

Tim Williams
Tim Williams

Reputation: 166196

Your regular module should look more like this (see below). This removes the logic from your ThisWorkbook module.

Option Explicit

Public CloseTime As Variant
Public SaveTime As Variant

Public Sub StartTimers()
    StartSaveTimer
    StartCloseTimer
End Sub
Public Sub CancelTimers()
    CancelSaveTimer
    CancelCloseTimer
End Sub

Sub StartSaveTimer()
    If ThisWorkbook.ReadOnly Then Exit Sub
    CancelSaveTimer 'remove any existing timer
    SaveTime = Now + TimeValue("00:30:00")
    Application.OnTime SaveTime, "SaveThis"
End Sub
Sub CancelSaveTimer()
    On Error Resume Next
    Application.OnTime EarliestTime:=SaveTime, Procedure:="SaveThis", Schedule:=False
    On Error GoTo 0
End Sub

Sub StartCloseTimer()
    If ThisWorkbook.ReadOnly Then Exit Sub
    CancelCloseTimer 'remove any existing timer
    CloseTime = Now + TimeValue("00:35:00")
    Application.OnTime CloseTime, "CloseThis"
End Sub
Sub CancelCloseTimer()
    On Error Resume Next
    Application.OnTime EarliestTime:=CloseTime, Procedure:="CloseThis", Schedule:=False
    On Error GoTo 0
End Sub

Public Sub CloseThis()
    On Error Resume Next
    CancelTimers
    ThisWorkbook.Close SaveChanges:=True
End Sub

Sub SaveThis()
    Application.DisplayAlerts = False
    ThisWorkbook.Save
    Application.DisplayAlerts = True
    StartSaveTimer
End Sub

Upvotes: 1

Related Questions