close function at workbooks (collection) level

lezawang

Well-known Member
Joined
Mar 27, 2016
Messages
1,805
Office Version
  1. 2016
Platform
  1. Windows
Hi
No one talk about "Workbooks.close" --> collection leve<collection level="">. I did google search and they are all talking about "Workbooks("name").close" <-- workbook level<workbook level="">

The code below, will close the first workbook no matter how many workbooks are open. My questions
1) How can I improve that code and control what I want to close for example ask user which workbooks he/she wants to close
Code:
 x=inputbox("enter workbook number (index) you want to close")
2) can that code imporve without using inputbox function to control what to close? Thank you so much

Code:
Sub april15_2()
    Workbooks.Close
End Sub
</workbook></collection>
 
Last edited:

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
There is no numerical index for open books, but you can know the order in which they were opened with a For Each.
Try this:


Code:
Sub test()
    Dim wb As Workbook, x As Variant, n As Long
    x = InputBox("enter workbook number (index) you want to close")
    If x = "" Then Exit Sub
    If IsNumeric(x) Then
        n = 1
        For Each wb In Workbooks
            If n = x Then
                wb.Close
                Exit Sub
            End If
            n = n + 1
        Next
    End If
End Sub
 
Upvote 0
Something like this:

Code:
Sub closeAll()
Dim i As Integer
Dim msg As String
Dim ids As Variant
    On Error Resume Next
    With Application.Workbooks
        For i = 1 To .Count
            If Not .Item(i) Is ThisWorkbook Then
                msg = msg & vbCr & i & "- " & .Item(i).Name
            End If
        Next i
        
        ids = Split(InputBox("Enter the workbook index numbers that you want to close separated by comma and click Ok." & vbCr & msg, "Close Workbooks"), ",")
    
        
        For i = UBound(ids) To 0 Step -1
            If Not .Item(Int(ids(i))) Is ThisWorkbook Then
                .Item(Int(ids(i))).Close False
            End If
        Next i
        
    End With
    On Error GoTo 0
End Sub

It doesn't have a proper error handling, but simply ignoring the wrong entries (like an item number higher than opened workbooks, or a wrong text entry). But it should give an idea.

Note that how I go from the upper item index to zero as items will get confused if you close a workbook with smaller item index.
Also note that I used "don't save" option in the close method as False. You can save by using True, or leave it blank to ask user.
Final note - it is obviously ignoring the workbook with the macro.

Hope it helps.

EDIT: In the first note, I didn't mention that I assumed user entered the item indexes in ascending order (i.e. 3,4,6, not 4,6,3). Array numbers could be sorted first as a solution, but as I said, this is just to give an idea.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,205
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top