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

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