The message pops up as soon as the file is opened.
Thanks for taking a look.
The entire code I am using is;
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
-------------------------------------------------------------------------
Public RunWhen As Double
Public Const NUM_MINUTES = 10
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
"Simon Lloyd" wrote:
I don't see where you get NUM_MINUTES from and you haven't supplied the
SaveAndClose code, you are using the variable RunWhen and haven't
declared it. When does the error occur on open or close?LiAD;333426 Wrote:
Hi,
I was given a code which has been working ok until recently, at least
as far
as I know but its just stopped working and I dont know why. Nothing has
been
altered in the file.
The following section of code shows up as;
Run time error '1004'
Method 'OnTime' of object '_Application' failed
Would anyone know what the issue could be?
VB shows the following line as the issue;
Application.OnTime RunWhen, "SaveAndClose", , False
which is contained with this part of a code
Code:
--------------------
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
The complete code is
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
--------------------
--
Simon Lloyd
Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=93176