Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

an autosave macro-- getting 1004 error"Method 'OnTime' of of object

  Asked By: Jayden    Date: Oct 01    Category: MS Office    Views: 2299
  

have been trying to set up an autosave
for the program I've been working on (which all of you have already
helped with a lot, already, thanks).

When I looked at the VBA help files (by the way, using Office
2003) and poked around on the web, including ozgrid
(http://www.ozgrid.com/Excel/run-macro-on-time.htm) as well as other
sites, I tried numerous ways to get it to work, and have not
succeeded yet.

The 1004 error I'm getting is in the ThisWorkBook code of:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime dTime, "autosavMacro", , Schedule:=False
End Sub

'<---which I understood was necessary to shut off the scheduled
'<---firing of the autosave, so the user could do other things
'<---that's what I understood from the ozgrid page mentioned above

Also involved is:

Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:05:00"), "autosavMacro"
End Sub

in a Standard module, I put:

Public dTime As Date

Sub autosavMacro()
dTime = Now + TimeValue("00:05:00")
Application.OnTime dTime, "autosavMacro"
'---and I tried: Application.OnTime dTime, "saveAllCallData"
Call saveAllCallData
End Sub

Of course, the problem with a 1004 error, is that it's hard to
see where exactly in the code is the problem. Ok, the method
failed . . . why? . . . heck if I know . . . I tried changing it
many ways, and even tried to run several versions of the code that I
had found online just pasted directly.

The saveAllCallData macro/subroutine works fine when called from
any other source, or when I just run it directly.
I did see the autosavMacro fire once. That's it, though. On the
close workbook/save attempts, though, keep hitting the 1004 error.

What am I missing? All suggestions will be appreciated.

Hope are of you are having a wonderful New Year, and as always
thanks for your help.

Share: 

 

6 Answers Found

 
Answer #1    Answered By: Scott Anderson     Answered On: Oct 01

I would put a breakpoint on the line
Sub autosavMacro()

and then use f8 to step through it.

 
Answer #2    Answered By: Abaddon Cohen     Answered On: Oct 01

I did put a breakpoint on the line "Sub autosavMacro" . . . and
stepped thru it fine, no problems there . . . when it autofired, and
when I just ran it independenly . . . that part appears to work in
all the various ways it is called.

The error happens as marked in the following:

The 1004 error I'm getting is in the ThisWorkBook code of:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime dTime, "autosavMacro", , Schedule:=False
'<---The error happens there
End Sub

'<---which I understood was necessary to shut off the scheduled
'<---firing of the autosave, so the user could do other things
'<---that's what I understood from the ozgrid page mentioned above

--that is, when the workbook is closed.
--And, by the way, after my last post here on the group, I got
the autosavMacro to fire every 5 minutes, like I was trying to get
it to do. As far as I can tell, the rest of it works fine. I just
get the 1004 error on that line only when I try to close the
workbook.

I've looked at that line forwards and backwards and tried several
ways to write it, and got the same error. Other advice or
suggestions, please?

 
Answer #3    Answered By: Jacqueline Long     Answered On: Oct 01

Can
anyone else give me a hand on this, please? I know everyone is
volunteering their time, so any help is appreciated.

 
Answer #4    Answered By: Joe Evans     Answered On: Oct 01

You guessed right. I am very busy at the moment and it is about 7 years since
I used OnTime. I would need to research the closing of the schedule. I would
have thought that closing the workbook would kill the schedule, but that may
not be the case.

In your autoopen you do not set dTime. Therefore if you close before the
first run of autosavmacro there will be no dtime for the before close macro  to
find (or it will have a value different from the value of the onTime process
you are trying to kill)

 
Answer #5    Answered By: Mable Stone     Answered On: Oct 01

Thanks for taking the time to reply even in the midst of a busy
schedule--I always appreciate your thoughts and suggestions.

I was wondering about the schedule part, as well . . . the ozgrid
page I mentioned seemed to say that closing would not kill the
schedule, but that may be from earlier versions of Excel, so I'm
still not sure.

That's a good point about the dTime . . . it didn't occur to me
that the value would change for the closing. I'll correct that and
see if that works. If it does, I'll let you know.

 
Answer #6    Answered By: Guillermo Cox     Answered On: Oct 01

I found the solution to the problem in Chip Pearson's
explanation on his site:
http://www.cpearson.com/excel/OnTime.aspx

So, it's working fine now. The code which I got to run
correctly was:

' ' 'the following placed in ThisWorkbook:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat,
Schedule:=False

End Sub

Private Sub Workbook_Open()
'<-some code for the opening steps of the program
StartTimer
End Sub

' ' 'the following placed in a standard module:

Public RunWhen As Double
Public Const cRunIntervalSeconds = 300 'that is, 5 minutes
Public Const cRunWhat = "autosavMacro"

Sub StartTimer()
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat,
Schedule:=True
End Sub

Sub StopTimer()
Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat,
Schedule:=False
End Sub

Sub autosavMacro()
Application.EnableEvents = False
'<-the code for the backup save steps of the program
StartTimer
Application.EnableEvents = True
End Sub

Chip agreed that the timer must be shut off, or it would keep
running after the workbook was closed, so it would try to open it
again.
This one sets it to automatically shut off when the wookbook is
closed, but if the user wants to shut it off sooner, the "StopTimer"
sub does that.

 




Tagged: