Add a New menu on Mouse right click for workbook Navigation

If you want to add a new menu on mouse right click "Workbook Navigation showing you the list of all open workbooks and worksheets in each of these workbooks. So that you can navigate easily. Snapshot below-

Add  below code to workbook open module

Private Sub Workbook_Open()

    On Error Resume Next

    'Delete the new button if already exists

    ' name of the new button is "Workbook Navigation"

    Application.CommandBars("Cell").Controls("Workbook Navigation").Delete

    'run a macro to add a new button on mouse right click

    Call add_new_button

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    On Error Resume Next

    ' delete the btton when workbook is closed.

    ' name of the new button is "Workbook Navigation"

    Application.CommandBars("Cell").Controls("Workbook Navigation").Delete

End Sub

Add below code to module1 or any new module

Option Explicit

Sub add_new_button()

    

    ' macro to add new button with name "Workbook Navigation"

    Dim cBut        As CommandBarControl

    On Error Resume Next

    ' name of the new button "Workbook Navigation"

    Application.CommandBars("Cell").Controls("Workbook Navigation").Delete

    Set cBut = Application.CommandBars("Cell").Controls.Add(Type:=msoControlPopup, Temporary:=True)

    ' name of the new button is "Workbook Navigation

    cBut.Caption = "Workbook Navigation"

    ' name of macro which you want to run when u will click on it

    cBut.OnAction = "new_button_macro"

    

End Sub

Sub new_button_macro()

Dim wk As Workbook

Dim wks As Worksheet

Dim cmda As CommandBarControl

Dim cbut2 As CommandBarControl, CBT3 As CommandBarControl

    For Each cmda In Application.CommandBars("Cell").Controls("Workbook Navigation").Controls

        On Error Resume Next

        cmda.Delete

    Next

    For Each wk In Application.Workbooks

        Set cbut2 = Application.CommandBars("Cell").Controls("Workbook Navigation").Controls.Add(Type:=msoControlPopup)

        With cbut2

            .Caption = wk.Name

            .OnAction = "activate_workbook"

        End With

        For Each wks In wk.Sheets

                Set CBT3 = cbut2.Controls.Add(Type:=msoControlButton)

                With CBT3

                    .Caption = wks.Name

                    .OnAction = "activate_sheet"

                If wks.Visible = True Then

                    .FaceId = 351

                Else

                    .FaceId = 352

                End If

            End With

        Next

    Next

End Sub

Sub activate_workbook()

On Error Resume Next

Windows(Application.CommandBars.ActionControl.Caption).Activate

End Sub

Sub activate_sheet()

    Dim ans As String

    ' check if sheet is hidden

    If ActiveWorkbook.Sheets(Application.CommandBars.ActionControl.Caption).Visible <> xlSheetVisible Then

        ans = MsgBox("This Worksheet is currently hidden. Do you want to unhide ? ", vbOKCancel, "Please Answer")

            If ans = vbOK Then

                ActiveWorkbook.Sheets(Application.CommandBars.ActionControl.Caption).Visible = True

                ActiveWorkbook.Sheets(Application.CommandBars.ActionControl.Caption).Select

            Else

                Exit Sub

            End If

        Else

        ActiveWorkbook.Sheets(Application.CommandBars.ActionControl.Caption).Activate

    End If

End Sub

Download Working Macro https://www.box.com/s/6902c9af265ae67733fd