Stop Auto_open macro running in other open workbooks

lenrog · Jun 28th 2016

Hi

I have an auto_open macro which auto refreshes a pivot table in workbook A on opening and the pivot refreshes every 1 minute. This works fine but when I open another unrelated Excel workbook B, the auto_open macro in workbook A runs again and re-opens workbook A even when I close down workbook A.

I've noticed that in the VBA editor that the VBA modules for workbook A appear in the VBA editor in workbook B even though I have closed down workbook A.

I've tried using Private sub auto_open in a module thinking it would stay away from other workbooks and workbook_open in the workbook but no luck. Could it be the timer I've added?

My code is tagged.

Code

Private Sub Auto_Open()


Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.DisplayStatusBar = True
Application.StatusBar = "UPDATING ..."




'Pivot table refresh


Dim pt As PivotTable
    For Each pt In ActiveSheet.PivotTables
        pt.RefreshTable
    Next pt


'Timer to rerun refresh every 1 minute


Application.OnTime Now + TimeValue("00:01:00"), "Auto_Open"            


Application.StatusBar = ""
Application.ScreenUpdating = True
Application.DisplayAlerts = True


End Sub

Display More

Infomage · Jun 28th 2016

Re: Stop Auto_open macro running in other open workbooks

Probably not the most elegant solution, but you could wrap all your pivot table code in an if condition, and test the activeworkbook.name property:

Code

if activeworkbook.name = "**your filename here**" then


'your code
Application.ScreenUpdating = False 
    Application.DisplayAlerts = False 
    Application.DisplayStatusBar = True 
    Application.StatusBar = "UPDATING ..." 
     
     
     'Pivot table refresh
     
    Dim pt As PivotTable 
    For Each pt In ActiveSheet.PivotTables 
        pt.RefreshTable 
    Next pt 
     
     'Timer to rerun refresh every 1 minute
     
    Application.OnTime Now + TimeValue("00:01:00"), "Auto_Open" 
     
    Application.StatusBar = "" 
    Application.ScreenUpdating = True 
    Application.DisplayAlerts = True 
end if

Display More

lenrog · Jun 28th 2016

Re: Stop Auto_open macro running in other open workbooks

Infomage

Wowser - this seems to have solved my problem although it reloads the first time but when I close it the second time it stays off.

Thanks very much for your advice.

**norie** · Jun 28th 2016

Re: Stop Auto_open macro running in other open workbooks

The problem is with Application.OnTime not AutoOpen, you need to cancel the OnTime when you close workbook A or Excel/VBA will keep on trying to run it and to do that it needs to open workbook B.

Infomage · Jun 29th 2016

Re: Stop Auto_open macro running in other open workbooks

If you only want the timer to run when the workbook with the pivots is active, use the workbook.activate and workbook.deactivate events to start and stop it respectively.

Stop Auto_open macro running in other open workbooks

Participate now!

Share