Hello,
I have code as follows in "Thisworkbook" codesheet:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Quitting As Integer
Dim wb As Workbook
Dim ws As Worksheet
With Application
If .Workbooks.Count 1 Then
For Each wb In .Workbooks
If wb.Name = ThisWorkbook.Name Then
'do nothing
Else
.EnableEvents = False
wb.Close False
.EnableEvents = True
End If
Next
End If
.ScreenUpdating = True
.ScreenUpdating = False
End With
Quitting = MsgBox("Save changes?", vbQuestion + vbYesNoCancel, "Cost
Distribution")
Application.ScreenUpdating = False
Select Case Quitting
Case vbYes
Application.DisplayAlerts = False
For Each ws In ActiveWorkbook.Worksheets
If ws.Name Like "Sheet*" Then
ws.Delete
End If
Next
ActiveWorkbook.Save
Case vbNo
ThisWorkbook.Saved = True
Case vbCancel
Cancel = True
Exit Sub
End Select
RestoreEnvironment
Application.DisplayAlerts = False
Application.Caption = Empty
Application.Quit
End Sub

It works fine when only the workbook with the code is open and closed.
However if other workbooks are opened and closed, firing the close event,
the procedure hangs not allowing Excel to quit. I have tried variations of
this code to no avail.
Any suggestions?

Regards,
Stefano Condotta

Hi
have tested your code under Excel 97 and 2003 and it works for me (you
have some screenupdating statements in your code which are not
necessary but that's not a problem).
Have you tried stepping through your code with a debugger (setting a
breakpoint in your Before_close event?.
Maybe you can than identify at which point your code hangs.

--
Regards
Frank Kabel
Frankfurt, Germany


Stefano Condotta wrote:
Hello,
I have code as follows in "Thisworkbook" codesheet:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Quitting As Integer
Dim wb As Workbook
Dim ws As Worksheet
With Application
If .Workbooks.Count 1 Then
For Each wb In .Workbooks
If wb.Name = ThisWorkbook.Name Then
'do nothing
Else
.EnableEvents = False
wb.Close False
.EnableEvents = True
End If
Next
End If
.ScreenUpdating = True
.ScreenUpdating = False
End With
Quitting = MsgBox("Save changes?", vbQuestion +
vbYesNoCancel, "Cost Distribution")
Application.ScreenUpdating = False
Select Case Quitting
Case vbYes
Application.DisplayAlerts = False
For Each ws In ActiveWorkbook.Worksheets
If ws.Name Like "Sheet*" Then
ws.Delete
End If
Next
ActiveWorkbook.Save
Case vbNo
ThisWorkbook.Saved = True
Case vbCancel
Cancel = True
Exit Sub
End Select
RestoreEnvironment
Application.DisplayAlerts = False
Application.Caption = Empty
Application.Quit
End Sub

It works fine when only the workbook with the code is open and
closed. However if other workbooks are opened and closed, firing the
close event, the procedure hangs not allowing Excel to quit. I have
tried variations of this code to no avail.
Any suggestions?

Regards,
Stefano Condotta