Time close and save

L

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 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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

RunWhen error 4
Impossible? - Close code 1
Close help 2
Help! Combine Macros 2
Code Stopped 2
Conflicting VBA Coding 7
Timer question 5
Excel can't find macro 2

Top