Problems closing a workbook with VBA
 

Hi everyone

I coded a button on a userform with Application.Quit - all was fine.

I run into problems if more than one file is open as all the file
close when the code is run.

I changed the code to ThisWorkbook.close

This is OK if more than one file is open however if the workbook is th
only one open I am left with the blue bar, menu bar and toolbars at th
top of a grey screen when I run the code.

Is there a way of saying

If <if there is more than one file open Then
ThisWorkbook.Close
Else
Application.Quit
End If


Any Suggestions?

TIA

Kenny
Win ME and 2000
Office 97 and 200

--
Message posted from http://www.ExcelForum.com

Problems closing a workbook with VBA
 

You can try:

Sub CloseBooks()

On Error GoTo OnlyOneOpen
If Len(Application.Workbooks(2).name) 0 Then
ThisWorkbook.Close
Exit Sub
End If
OnlyOneOpen: Application.Quit
End Sub


Haven't tested so I am not sure if it works but I belive it is OK.

Best Regards

Noo

--
Message posted from http://www.ExcelForum.com

Problems closing a workbook with VBA
 

And of course I think you can substitute the "<if there is more than on
file open" in your equation by:

Workbooks.Count 1

Best Regards

Noo

--
Message posted from http://www.ExcelForum.com

Problems closing a workbook with VBA
 

Thanks Nook

That did the trick - or so I thought - extensive testing revealed m
problem of the menu and toolbars remaining with a grey screen was du
to Personal.xls If I removed this from excel then ran my fil
everything was Ok - your assistance with workbooks.count solved m
problem with multiple files being open.

As a compromise I have added code to close Personal.xls when I open m
file. This isn't really fixing the bug but it was decided that this i
better than closing down to a grey screen.

Now I am trying to write If Personal.xls exists, close it! as a
present if Personal.xls does not exist I get an error (supperssed by o
Error Resume Next)

Many thanks again

Kenn

--
Message posted from http://www.ExcelForum.com