Responding to an IM and the post #6 Bat Signal...
Wow, this post goes back more than 7 years. Good for you to have done the preliminary searching. Here's a creative and easy way to activate other open workbooks, using a custom right click event menu.
Please follow these directions exactly as I write them.
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:
Code:
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:
Code:
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.
