VBA Express : Excel - Customizing AutoSave Programmatically

Excel

Customizing AutoSave Programmatically

Ease of Use

Easy

Version tested with

2003

Submitted by:

Paleo

Description:

Make sure your user is gonna save his/her workbook after a certain amount of time.

Discussion:

Sometimes we want to assure the user will have his/her workbook saved at a certain amount of time regardless his/her autosave settings. Save the workbook as a template for the functionality to apply to new workbooks based on the template. Adjust the TimeOut value to suit your users.

Code:

instructions for use

			 '//Insert the following in the Workbook module
 
Private Sub Workbook_Open() 
    Call Tempo 
End Sub 
 
Private Sub Workbook_BeforeClose(Cancel As Boolean) 
    Call Limpa 
End Sub 
 
 '//Insert the following in a standard module
 
Public vartimer As Variant 
Const TimeOut = 5 'in minutes
 
Sub Salva() 
    ActiveWorkbook.Save 
    Call Tempo 
End Sub 
 
Sub Tempo() 
    vartimer = Format(Now + TimeSerial(0, TimeOut, 0), "hh:mm:ss") 
    If vartimer = "" Then Exit Sub 
    Application.OnTime TimeValue(vartimer), "Salva" 
End Sub 
 
Sub Limpa() 
    On Error Resume Next 
    Application.OnTime earliesttime:=vartimer, _ 
    procedure:="Salva", schedule:=False 
    On Error GoTo 0 
End Sub

How to use:

Copy the Private Sub Workbook_Open and the Private Sub Workbook_BeforeClose codes
Open any workbook.
Press Alt + F11 to open the Visual Basic Editor (VBE).
Press Ctrl + R to show the Project Explorer.
Double-click This_Workbook
Paste the Private Subs into the right-hand code window.
Click Insert from the VBE Command Bar and select Module
Double-click the new module
Copy the rest of the code
Paste them on the new module
Close the VBE and save the file
Close the file
Re-Open the file

Test the code:

Open a file with the above code and you will see it working (the example saves the workbook every 5 minutes)

Sample File:

SavingWorkbook.zip 8.51KB

Approved by mdmackillop

This entry has been viewed 297 times.