Excel Application.Quit only works when stepping though

[SteveFairclough]

I have a simple macro that automatically starts when the spreadsheet is opened. I am desperately trying to get the macro to automatically close excel at the end. However, Excel will only quit when I'm stepping though the code to debug it, if I run the code normally then all the other code executes fine but when it gets to the Application.Quit line, excel just doesn't do anything. I'm using the "Application.DisplayAlerts = False" method to quit Excel without saving. A shortened version of my code is shown below :-

Sub Autpen()
'Main part of code goes here
Application.DisplayAlerts = False
'placing a Stop here and stepping though will then allow the following line to execute !!
Application.Quit
End Sub

Does anybody have any idea what is going on here ?

Many thanks


Steve Fairclough
I.T. Manager

 

[dyarwood]

It could be that the DisplayAlerts are hiding a Save prompt. I would suggest turning them on before you use the Quit. Alternatively use the ActiveWorkBook.Close and choose whether to save it.

 

[SteveFairclough]

The only problem with that is the Application.Quit line will not then execute as the macro will be closed down with the workbook. However, your suggestion did prompt me to change my method slightly by saving the workbook first using ActiveWorkbook.Save and then quitting but it still does not work. However, once again, if I put a Stop immediately before the Application.Quit line and step though manually then Excel does actually quit. Very frustrating !

Steve Fairclough
I.T. Manager

 

[sdraper]

It sounds like the code prior to the applicaiton.quit command is still working and disallowing the the command to execute.

Sam

 

[SteveFairclough]

Sam

How would I know this ? I've now tried putting in a 20 second delay before the Application.Quit command to allow for any backgound stuff that may be going on. Still doesn't work though.

Steve Fairclough
I.T. Manager

 

[sdraper]

Can you show us the originally omitted code?

Sam

 

[SteveFairclough]

There's quite a bit of code, a bit too much to paste into hear. However, I think I'll comment out most of it and re-introduce the code bit by bit and hopefully identify the offending code by a process of elimination.

I'll let you know how it goes..

Steve Fairclough
I.T. Manager

 

[sdraper]

SOunds like a good plan. Keep us posted!

Sam