Hi Tim,

This code should work. FWIW, I tried your code in both XL 2002 and 2003,
and I experienced no problems. What version of XL are you running? Is it
up to date and fully-patched? What OS? Do you have any third-party add-ins
installed (or anything in XLStart)? If so, you should try unchecking all of
them and running your code again. If you don't have any problems, add the
add-ins back in one by one until you experience the crashes again.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


Tim Mayes wrote:
Hi all,

I need a macro that checks for other open workbooks and, if they are
there, closes them (without saving). I'm using Workbook_Activate so
that this check will be done anytime a user changes to another
workbook and back again. Here's my code:

Private Sub Workbook_Activate()
Dim i, NumOpen As Integer
Dim w As Workbook
NumOpen = Application.Workbooks.Count
MsgBox "There are " & NumOpen & " workbooks open"
If NumOpen 1 Then
For Each w In Workbooks
If (w.Name < ThisWorkbook.Name And w.Name < "PERSONAL.XLS")
Then MsgBox w.Name & " will be closed"
Application.Workbooks(w.Name).Close savechanges:=False
End If
Next w
End If
End Sub

This all works just dandy, except that if a workbook gets closed Excel
crashes. If there are no other open workbooks, there is no problem. The
troublesome line of code is:

Application.Workbooks(w.Name).Close savechanges:=False

I've tried several variations on that line which all result in the
same problem (e.g. just using w.close). It does close the other
workbook, but then it crashes. What seems to happen is that it goes
through the loop, closes the workbook, and then exits the sub but
then restarts the sub and that causes the crash.

Any help would be appreciated.

Thanks,

Tim