Macro stills runs after workbook closes 
Author Message
 Macro stills runs after workbook closes

Hi all,

I'm trying to get a spreadsheet to autosave every 20 seconds.  I can get
this to work, however, after I close my spreadsheet, a few seconds later
it re-opens on it's own.  I guess I'm not closing the macro?  Can
anybody tell me what I'm doing wrong:
---
Private Sub Workbook_Open()

    Call QuickSave

End Sub
----
Private Sub Workbook_Close()

    Application.OnTime Now, "ThisWorkbook.QuickSave", , False

End Sub
----
 Sub QuickSave()

    ThisWorkbook.Save
    Application.OnTime Now + TimeValue("00:00:20"),
"ThisWorkbook.QuickSave"

End Sub



Wed, 27 Jun 2001 03:00:00 GMT  
 Macro stills runs after workbook closes
Richard,

You have to "unschedule" the OnTime event.  The scheduled time is the
"key" that the Application uses to manage times.  To cancel it, you
must provide EXACTLY the same time.

Store your time in a Public (global) variable, and the use that time
to cancel the OnTime event.

Public WhatTime As Double

Sub QuickSave()

    ThisWorkbook.Save
    WhatTime = Now + TimeValue("00:00:20")
    Application.OnTime WhatTime, "ThisWorkbook.QuickSave"

End Sub

Private Sub Workbook_Close()

    Application.OnTime WhatTime, "ThisWorkbook.QuickSave", , False

End Sub

Cordially,
Chip Pearson
http://home.gvi.net/~cpearson/excel.htm

Quote:

>Hi all,

>I'm trying to get a spreadsheet to autosave every 20 seconds.  I can
get
>this to work, however, after I close my spreadsheet, a few seconds
later
>it re-opens on it's own.  I guess I'm not closing the macro?  Can
>anybody tell me what I'm doing wrong:
>---
>Private Sub Workbook_Open()

>    Call QuickSave

>End Sub
>----
>Private Sub Workbook_Close()

>    Application.OnTime Now, "ThisWorkbook.QuickSave", , False

>End Sub
>----
> Sub QuickSave()

>    ThisWorkbook.Save
>    Application.OnTime Now + TimeValue("00:00:20"),
>"ThisWorkbook.QuickSave"

>End Sub



Wed, 27 Jun 2001 03:00:00 GMT  
 Macro stills runs after workbook closes
Hi Chip,  Thanks for the tips.  Unfortunately, it's still not working for
me; my Workbooks keep re-opening after closing.  I'm sure I'm missing
something very basic but I'm pretty new at VBA.

My code is:
----
Public WhatTime As Double
----
Private Sub Workbook_Close()
    Application.OnTime WhatTime, "ThisWorkbook.QuickSave", , False
End Sub
---
Private Sub Workbook_Open()
    Call QuickSave
End Sub
---
Sub QuickSave()
    ThisWorkbook.Save
    WhatTime = Now + TimeValue("00:00:20")
    Application.OnTime WhatTime, "ThisWorkbook.QuickSave"
End Sub
----
When I click on my Workbook_Close routine, the dropdown box shows General
as opposed to my Workbook_Open routine which says Workbook.  Could this
be causing the problem?  I don't have a Workbook_Close in my right
dropdown box, so I tried the BeforeClose statement which gives me an
error sometimes upon closing the Workbook (although it doesn't re-open).
Any tips would be appeciated.  Thanks.

Quote:

> Richard,

> You have to "unschedule" the OnTime event.  The scheduled time is the
> "key" that the Application uses to manage times.  To cancel it, you
> must provide EXACTLY the same time.

> Store your time in a Public (global) variable, and the use that time
> to cancel the OnTime event.

> Public WhatTime As Double

> Sub QuickSave()

>     ThisWorkbook.Save
>     WhatTime = Now + TimeValue("00:00:20")
>     Application.OnTime WhatTime, "ThisWorkbook.QuickSave"

> End Sub

> Private Sub Workbook_Close()

>     Application.OnTime WhatTime, "ThisWorkbook.QuickSave", , False

> End Sub

> Cordially,
> Chip Pearson
> http://home.gvi.net/~cpearson/excel.htm


> >Hi all,

> >I'm trying to get a spreadsheet to autosave every 20 seconds.  I can
> get
> >this to work, however, after I close my spreadsheet, a few seconds
> later
> >it re-opens on it's own.  I guess I'm not closing the macro?  Can
> >anybody tell me what I'm doing wrong:
> >---
> >Private Sub Workbook_Open()

> >    Call QuickSave

> >End Sub
> >----
> >Private Sub Workbook_Close()

> >    Application.OnTime Now, "ThisWorkbook.QuickSave", , False

> >End Sub
> >----
> > Sub QuickSave()

> >    ThisWorkbook.Save
> >    Application.OnTime Now + TimeValue("00:00:20"),
> >"ThisWorkbook.QuickSave"

> >End Sub



Wed, 27 Jun 2001 03:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Macro to open a workbook, work in that workbook, than close the workbook (saved)

2. Running a macro on closing my workbook

3. Excel closing a workbook out while running a macro

4. Excel 5.0: Run macro when workbook is saved (not closed)

5. Macro to run when closing a workbook.

6. Closing a workbook without killing the macro that is running

7. Closing 1 workbook closes all workbooks

8. Macro to Run Macros in Closed Files

9. Excel Process running even after closing excel workbook

10. Closing an excel workbook opened through Internet Exlporer with a macro


 
Powered by phpBB® Forum Software © phpBB Group