Tom’s Tutorials for Excel: Closing open workbooks with unknown names using Excel.
Start by closing all workbooks in Excel except for the workbook that you want this functionality for.
In this lone open workbook, from any worksheet press Alt+F11 to go into the Visual Basic Editor. Careful, that’s ALT + F11.
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)
Expand the yellow Objects folder.
Right click on the object named ThisWorkbook, and left click to select ViewCode.
Paste this code into the large white window pane that is the workbook module:
Private Sub Workbook_Open()
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”
Private Sub Workbook_Activate()
Private Sub Workbook_Deactivate()
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
If Workbooks.Count = 1 Then Exit Sub
Cancel = True
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
Private Sub RightKlickMenuMayker()
Dim objBar As Object, objBtn As Object, i As Integer
Set objBar = Application.CommandBars.Add(“wbNavigator”, msoBarPopup)
For i = 1 To Workbooks.Count
Set objBtn = objBar.Controls.Add
.Caption = IIf(wb.Name = ThisWorkbook.Name, wb.Name & ” (this workbook)”, wb.Name)
.Style = msoButtonCaption
.OnAction = “wbActivayte”
Private Sub wbActivayte()
Save your workbook.
Close your workbook.
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