Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Stopped
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Stopped
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Stopped
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can someone please tell me why my code stopped working??? | Excel Worksheet Functions | |||
Code Stopped Working | Excel Programming | |||
Code stopped working | Excel Programming | |||
vb code stopped running | Excel Programming | |||
Tab stopped working | Excel Programming |