Impossible? - Close code

[LiAD]

Hi,

I am trying to use this code to hide the formula bars, menus etc and save
and close a file after a certain time has passed, but i can't get it to run.
It errors on this line (line is in two places and both error).

Any ideas how I can fix this?

Entered as a workbook code

Application.OnTime RunWhen, "SaveAndClose", , False

Private mFormulaBar

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim oCB As CommandBar
On Error Resume Next
Application.OnTime RunWhen, "SaveAndClose", , False
On Error GoTo 0
For Each oCB In Application.CommandBars
oCB.Enabled = True
Next oCB

Application.DisplayFormulaBar = mFormulaBar

Application.Quit
End Sub

Private Sub Workbook_Open()
Dim oCB As CommandBar
On Error Resume Next
Application.OnTime RunWhen, "SaveAndClose", , False
On Error GoTo 0

Application.OnKey "%{F11}", "dummy"

For Each oCB In Application.CommandBars
oCB.Enabled = False
Next oCB

mFormulaBar = Application.DisplayFormulaBar
Application.DisplayFormulaBar = False

RunWhen = Now + TimeSerial(0, NUM_MINUTES, 0)
Application.OnTime RunWhen, "SaveAndClose", , True
End Sub

Followed by a module of -


Public RunWhen As Double
Public Const NUM_MINUTES = 5

Public Sub SaveAndClose()
ThisWorkbook.Close savechanges:=True
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
On Error Resume Next
Application.OnTime RunWhen, "SaveAndClose", , False
On Error GoTo 0
RunWhen = Now + TimeSerial(0, NUM_MINUTES, 0)
Application.OnTime RunWhen, "SaveAndClose", , True
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)

On Error Resume Next
Application.OnTime RunWhen, "SaveAndClose", , False
On Error GoTo 0
RunWhen = Now + TimeSerial(0, NUM_MINUTES, 0)
Application.OnTime RunWhen, "SaveAndClose", , True

End Sub

 

[Alan McQuaid via OfficeKB.com]

Hi,

Go to http://www.cpearson.com/excel/OnTime.aspx , this helped me with using
OnTime

Alan