Auto_Close() not running

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.



Steve Barnett wrote:
>
> 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

--

Dave Peterson

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


"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
>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.
>
>
>
> Steve Barnett wrote:
>>
>> 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
>
> --
>
> Dave Peterson

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
> "Dave Peterson" <[email protected]> wrote in message
> news:[email protected]...
>>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.
>>
>>
>>
>> Steve Barnett wrote:
>>>
>>> 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
>>
>> --
>>
>> Dave Peterson
>
>