How to make Excel 2007 quit automatically

[Andrew Bourke]

Hi
This

If Workbooks.Count < 1 Then Application.Quit


works in Excel 2003 but Excel 2007 tries to restart.
Is there something else I should try ?


TIA

Andrew

 

[Ron de Bruin]

You can try this to close and save the workbook and
if it is the only file that is open close Excel

Dim wb As Workbook
Dim I As Integer

For Each wb In Application.Workbooks
If wb.Windows(1).Visible Then I = I + 1
Next wb
If I = 1 Then
Application.Quit
ThisWorkbook.Close SaveChanges:=True
Else
ThisWorkbook.Close SaveChanges:=True
End If

 

[Andrew Bourke]

Thanks for your reply Ron.

The CloseOff macro I am trying to fix works fine in Excel 2003 (I need
it to work in both Excel 2003 and Excel 2007).

This is the code I have been using and when it runs in Excel 2007,
a window pops up saying Excel has stopped working, its looking for a
solution to the problem, and then , Excel is re-starting, and it restarts.

Inserting the line

ThisWorkbook.Close SaveChanges:=True

causes an error saying:
"This workbook is currently being referenced by another workbook and
cannot be closed", even after the other workbook has been closed.


'--- CLOSE NO SAVE - quit if this is the only book open
If AA.Caller = "Quit3" Then
ActiveWorkbook.Saved = True
ActiveWorkbook.Close
If Workbooks.Count < 1 Then
AA.DisplayAlerts = False
ThisWorkbook.Close SaveChanges:=True
AA.Quit
End If
Exit Sub
End If

Any other ideas would be appreciated.
Thank you
Andrew Bourke

 

[Ron de Bruin]

Now I know your problem

See this page (bottom part :Code run from a shape can crash Excel 2007)
http://www.rondebruin.nl/shape.htm

 

[Andrew Bourke]

Thanks for your help Ron.
I took your advice and inserted forms buttons and now it works properly.