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

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

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Andrew Bourke" wrote in message ...
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

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














On 26/01/2009 11:52 PM, Ron de Bruin wrote:
ThisWorkbook.Close SaveChanges:=True

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

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Andrew Bourke" wrote in message ...
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














On 26/01/2009 11:52 PM, Ron de Bruin wrote:
ThisWorkbook.Close SaveChanges:=True

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

On 27/01/2009 7:16 AM, Ron de Bruin wrote:
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