Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

An AutoSave Add-In for Excel XP

Best of Excel

An AutoSave Add-In for Excel XP

by  Bowers74  Posted    (Edited  )
I must admit that I was a bit upset when I realized that Excel XP didn't have the AutoSave feature anymore, but I figured that even I could create a customizable AutoSave Add-in with the knowledge I have of VBA programming, instead of copying the one from Excel 2000.

Here is the code that I used at the beginning. My AutoSave Add-in has now been so far customized that I can't post the code for it here, but the generic version works just as well.

The following code allows you to save all of the Open Workbooks and gives you the "option" of turning the AutoSave function off for the current session of Excel (By pressing "No").

The code is set to run every 5 minutes, but you can change that as well.

Insert the following code into the Workbook code module
Code: 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.OnTime Now + TimeValue("00:05:00"), "AutoSave", , False
End Sub

Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:05:00"), "AutoSave"
End Sub

Create a standard module and enter the following code:
Code: 
Sub AutoSave()
Dim wb As Workbook
Dim msg As String
msg = "Do you want to save all of the open Workbooks now?" & vbCrLf & vbCrLf
msg = msg & "Selecting ""No"" disables the Auto Save function."
If MsgBox(msg, vbQuestion + vbYesNo, "Auto Save") = vbYes Then
    For Each wb In Application.Workbooks
        wb.Save
    Next wb
Application.OnTime Now + TimeValue("00:05:00"), "AutoSave"
End If
End Sub
Now, save the workbook as an Excel Add-In (.xla) and "install" it. Tools->Add-Ins.... You may have to browse for the file (depending on where you saved it).

As I stated before, you can customize you version of the Add-in as much as you like. My versions (I use one at home and two different ones at work, depending on which PC I'm on) range from the code you see above to an Add-in with UserForms and Toolbar Button COntrols for different options.

I hope you find this helpful!
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top