Autoclose code from Bob Phillips not working for me
 

Oh wise ones,
I searched from some code in this group to
automatically save and close a workbook after a specified amount of time. Bob
had posted this last November:

There are 4 elements here.


Firstly have a macro which invokes Ontime to run a macro that saves the
workbook in 5 minutes. And you also need a macro to save the workbook and
reset the timer

Option Explicit


Public nSaveWB As Date


Public Sub SetSaveWBTimer()
nSaveWB = Now + TimeSerial(0, 5, 0) ' 5 minutes
Application.OnTime nSaveWB, "SaveWB"
End Sub


Public Sub SaveWB()
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub


You then need to set the timer in the first place, when the workbook opens.
And you also need tyo trap any workbook changes so that the timer gsets
cancelled, and set anew (note this doesn't reset the timer for any
formatting changes, only data changes).


Option Explicit


Private Sub Workbook_Open()
SetSaveWBTimer
End Sub


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Application.OnTime nSaveWB, "SaveWB", , False
SetSaveWBTimer
End Sub


'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


I tried to adapt it for my workbook "timesheet" but I got a maco error
saying the macro S:/path/timesheet!SaveWB does not exist. Which it doesn't by
that name.
What am I doing wrong. I put this in as a workbook event.

Option Explicit

Public nSaveWB As Date

Public Sub SetSaveWBTimer()
nSaveWB = Now + TimeSerial(0, 5, 0) ' 5 minutes
Application.OnTime nSaveWB, "SaveWB"
End Sub

Public Sub SaveWB()
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub

Private Sub Workbook_Open()
SetSaveWBTimer
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Application.OnTime nSaveWB, "SaveWB", , False
SetSaveWBTimer
End Sub

If I put the public stuff in a module it shows up as a macro, but it doesn't
do anything.

Thanks,
Mike K

Windows XP
XP Excel