Tom’s Tutorials for Excel: Closing open workbooks with unknown names

Tom’s Tutorials for Excel: Closing open workbooks with unknown names using Excel.

Step 1
Start by closing all workbooks in Excel except for the workbook that you want this functionality for.

Step 2
In this lone open workbook, from any worksheet press Alt+F11 to go into the Visual Basic Editor. Careful, that’s ALT + F11.

Step 3
Hit Ctrl+R.

Step 4
In the Project – VBA Project window that is in the vertical pane on the left, find your workbook name in bold font. It will look like VBAProject (YourWorkbookName.xls)

Step 5
Expand the yellow Objects folder.

Step 6
Right click on the object named ThisWorkbook, and left click to select ViewCode.

Step 7
Paste this code into the large white window pane that is the workbook module:

Private Sub Workbook_Open()
Run “RightKlickMenuMayker”
MsgBox “Right click any worksheet cell for a list of open workbooks” & vbCrLf & _
“that you can click to select and immediately navigate to.”, 64, “Navigation tip”
End Sub

Private Sub Workbook_Activate()
Run “RightKlickMenuMayker”
End Sub

Private Sub Workbook_Deactivate()
Run “RightKlickMenuReeset”
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Run “RightKlickMenuReeset”
End Sub

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
If Workbooks.Count = 1 Then Exit Sub
Cancel = True
Application.CommandBars(“wbNavigator”).ShowPopup
End Sub

Step 8
While you are in the VBE, from the menu bar at the top, click Insert > Module, and paste this code into that new module:

Private Sub RightKlickMenuReeset()
On Error Resume Next
Application.CommandBars(“wbNavigator”).Delete
Err.Clear
End Sub

Private Sub RightKlickMenuMayker()
Dim objBar As Object, objBtn As Object, i As Integer
Run “RightKlickMenuReeset”
Set objBar = Application.CommandBars.Add(“wbNavigator”, msoBarPopup)
For i = 1 To Workbooks.Count
Set objBtn = objBar.Controls.Add
With objBtn
.Caption = IIf(wb.Name = ThisWorkbook.Name, wb.Name & ” (this workbook)”, wb.Name)
.Style = msoButtonCaption
.OnAction = “wbActivayte”
End With
Next i
End Sub

Private Sub wbActivayte()
Workbooks(Application.Caller(1)).Activate
End Sub

Step 9
Press Alt+Q.

Step 10
Save your workbook.

Step 11
Close your workbook.

Step 12
Open your workbook and any other workbooks, and when your workbook is activate, from any worksheet, you can right click a cell to see a pop up list of other open workbooks. Left click to select the workbook name you want to activate.

Note that the custom right click menu will only appear if more than one workbook is open in that same instance of Excel, and it will only appear in the subject workbook where the code is housed. As you might have noticed, as a personal programming style I purposely spell the names of my macros and variables phonetically, to help me differentiate them from similar module names.Close

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Leave a Reply

Your email address will not be published.

*