Macro Re-opens Workbook After Closing

Hi All,

I have a workbook that I have put a Workbook_Open macro in:-

Sub Workbook_Open()
Timer
End Sub

This runs a timer:-

Sub Timer()
Application.OnTime Now + TimeValue("00:05:00"), "RefreshAllTables"
End Sub

Which refreshes my data, below:-

Sub RefreshAllTables()

ActiveWorkbook.RefreshAll
Timer

End Sub

Firstly, I'm sure these can all be put together in one macro (I'm a complete novice at this) but, interestingly, when I close the workbook, the macro still appears to be running and, when it refreshes the data, it re-opens the workbook.

Can I put an Auto_Close in that will stop the macro running but will not affect anything that may be running at the same time.

As always, any help is much appreciated.

Feastie.

It looks as if you are running the Timer Sub twice - Once when the workbook opens and again in the "RefreshAllTables" Sub. Was this intentional?

I was trying to get it back to the Timer macro, I guess you're saying it doesn't stop counting just because it has focussed on the update?
Would that cause the issue with the re-open?

Hi Feastie

Please wrap your Code in Code Tags.

When you close the Workbook you need to stop the Timer.

In the ThisWorkbook Module...

Please Login or Register  to view this content.

In a General Module...

Please Login or Register  to view this content.

Thanks Jaslake,

apologies for the format, I've never posted code before.
Should I be adding these to what I already have or am I replacing some of it?

Thanks, Feastie.

Hi Feastie

You will NOT be replacing anything.

If you HAVE a WorkBook Before Close Event ADD this Line of Code

Please Login or Register  to view this content.

Otherwise past the ENTIRE Code into ThisWorkbook Module.

Please Login or Register  to view this content.

This Code is NEW and does not REPLACE anything. Paste the ENTIRE Code in a General Module.

Please Login or Register  to view this content.

Hi Feastie

I've been informed that the Stop Timer Code needs to be modified...let me get back to you.

Sorry for the inconvenience.

Hi Feastie

In Workbook.Open Code, replace this

Please Login or Register  to view this content.

with this (my personal preference)

Please Login or Register  to view this content.

Also, place this in the Workbook Module.

Please Login or Register  to view this content.

In a General Module:

Replace this

Please Login or Register  to view this content.

with this

Please Login or Register  to view this content.

Add this

Please Login or Register  to view this content.

Also, in that General Module, place this line if Code at the very Top of the Module.

Please Login or Register  to view this content.

In your "RefreshAllTables" Code replace this Line

Please Login or Register  to view this content.

with this

Please Login or Register  to view this content.

A tested sample File is attached for your review.

Thanks Jaslake,

it works perfectly, reputation added to.

Feastie.

You're welcome...glad I could help. Thanks for the Rep.

And a Thanks to shg...