Auto_Close() not running

  • Thread starter Thread starter Steve Barnett
  • Start date Start date
S

Steve Barnett

I have three identical (bar the data) spreadsheets which get loaded at the
same time. They all contain an auto_close() macro.

If I close all three spreadsheets, one at a time, then the auto_close()
macro runs in each spreadsheet, so allowing me to clean up properly and
allowing me to avoid the "do you want to save" prompt. However, if I click
the close button for Excel, only one of the auto_close() macros runs. The
other two spreadsheets prompt the user to save the file.

I've put break points in the auto_close() macros for all three spreadsheets
and only one auto_close() is being run.

Anyone know whether this is by design or a bug and whether there is a
workaround for this behaviour?

Thanks
Steve
 
I just tried this in xl2003 and auto_close ran for all the workbooks.

What version of excel are you running?

And do you have any code in the first auto_close that might be closing the other
workbooks.

If you do, then you'll have to run that auto_close routine yourself.

There's a nice example in VBA's help -- look for RunAutoMacros.
 
I'm running this in Excel2003 also, so I'm very confused. As far as I can
see there is nothing in the code that would cause the other workbooks to
close. All I do is reset the changes I made to the menu and unprotect a few
sheets.

I guess I'll have to keep looking...

My Auto_Close contans:

sub Auto_Close()
Luc_Auto_Close
end Sub

Sub Luc_Auto_Close()
'***Restore default Excel menu bar
Application.Run macro:="ResetMenu"

'***Turn off screen updating during macro
Application.ScreenUpdating = False

'***Unprotect all worksheets
Sheets("Benefits reporting").Select
ActiveSheet.Unprotect
Sheets("Programme reporting").Select
ActiveSheet.Unprotect
Sheets("Costs reporting").Select
ActiveSheet.Unprotect
Sheets("TLB breakdown").Select
ActiveSheet.Unprotect
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets(1).Activate
Range("A1").Select

'***Save workbook
ActiveWorkbook.Save

'***Turn on screen updating during macro
'Application.ScreenUpdating = True
End Sub
 
Got it!

As you can see below, the Auto_Close macro just calls a subroutine called
Luc_Auto_Close which is held in another module in the workbook.As coded,
this Luc_Auto_Close macro is called once regardless of how many workbooks I
have loaded.

When I moved the code from Luc_Auto_Close in to the Auto_Close sub,
everything started working properly again. Every workbook calls it's
Auto_Close macro and they all run to completion.

Not at all what I would have expected.

Steve


Steve Barnett said:
I'm running this in Excel2003 also, so I'm very confused. As far as I can
see there is nothing in the code that would cause the other workbooks to
close. All I do is reset the changes I made to the menu and unprotect a
few sheets.

I guess I'll have to keep looking...

My Auto_Close contans:

sub Auto_Close()
Luc_Auto_Close
end Sub

Sub Luc_Auto_Close()
'***Restore default Excel menu bar
Application.Run macro:="ResetMenu"

'***Turn off screen updating during macro
Application.ScreenUpdating = False

'***Unprotect all worksheets
Sheets("Benefits reporting").Select
ActiveSheet.Unprotect
Sheets("Programme reporting").Select
ActiveSheet.Unprotect
Sheets("Costs reporting").Select
ActiveSheet.Unprotect
Sheets("TLB breakdown").Select
ActiveSheet.Unprotect
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets(1).Activate
Range("A1").Select

'***Save workbook
ActiveWorkbook.Save

'***Turn on screen updating during macro
'Application.ScreenUpdating = True
End Sub
Click to expand...
 
Back
Top