Close help

[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.
Unfortunately I have reached the limit of what i know to try to fix the
problem and get it to run. It errors on this line (line is in two places and
both error).

----Application.OnTime RunWhen, "SaveAndClose", , False ------

Any ideas how I can fix this?
Thanks
LiAD

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

 

[Patrick Molloy]

haven't asked this already?

anyway
if this single line is by itself in the code page for ThisWorkbook, then
remove it

Application.OnTime RunWhen, "SaveAndClose", , False

code must be enclosed in a SUB or Function structure


change this
Private mFormulaBar
to
Public mFormulaBar

you need to tell us what the errors are.
At the top of each Module, type
OPTION EXPLICIT
this will not just force you to explicitly declare variables, its a great
way to prevent typos. Also, before you run code, use the menu Debug/Compile
VBAProject item - this will highlight obvious errors.
You need a sub named "dummy" in a standard module in order for this line to
compile:
Application.OnKey "%{F11}", "dummy"

 

[LiAD]

Cheers.

Works now just be removing the two lines and changing priv-public.

Yeah I had already asked, twice actually, but didnt make any progress.

Thanks a lot for your help