Close All Open Workbooks Except......

Andrew5 · Feb 25th 2008

Hi all,

I have what is probably a simple question but I can’t figure it out.

I made an Excel based program that uses multiple workbooks with two main workbooks (“Master List” and “Products”) that all the others pull information from.

I run into a problem when a user clicks the close X in the upper right hand corner and the entire application closes.

I found some code that will let me close all the workbooks but one (the code is put in the “Products” workbook in the BeforeClose event).

Code

For Each wb In Workbooks
	If Not wb Is ThisWorkbook Then
		wb.Close SaveChanges:=True
	End If
Next wb

Is there a way to modify the code so it will close all the workbooks but the two main ones if someone clicks the close X button in the upper right hand corner?

Thank you,
Andy

Dave Hawley · Feb 25th 2008

Re: Close All Open Workbooks Except Two Main Workbooks

Code

Dim wb As Workbook
For Each wb In Workbooks
    Select Case Ucase(wb.name)
        Case "BOOK1.XLS", "BOOK2.XLS", Ucase(ThisWorkbook.name)
            'Code, if any
        Case Else
            wb.Close SaveChanges:=True
    End Select
Next wb

Bill Rockenbach · Feb 25th 2008

Re: Close All Open Workbooks Except......

IF you don't want th eusers to use the close "X" insert this code in the userform code module.

Code

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)


    If CloseMode = vbFormControlMenu Then
        MsgBox "You must use the buttons at the bottom of this window.", , ""
        Cancel = True
    End If


End Sub

Andrew5 · Feb 25th 2008

Re: Close All Open Workbooks Except......

Thank you, Thank you, Thank you Dave.

That is exactly what I needed it to do.

Andy

Close All Open Workbooks Except......

Participate now!

Share