Excel

Add a New menu on Mouse right for workbook Navigation

Ease of Use

Intermediate

Version tested with

2007 

Submitted by:

ashish koul

Description:

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

Discussion:

 

Code:

instructions for use

			

Add this To workbook Module Private Sub Workbook_Open() On Error Resume Next Application.CommandBars("Cell").Controls("Browse Workbooks").Delete Call CREATE_MENU_my_menu End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) On Error Resume Next Application.CommandBars("Cell").Controls("Browse Workbooks").Delete End Sub '------------------------------------------------------------------------------------------------------------- Add this code To module1 Or In any New module Option Explicit Sub CREATE_MENU_my_menu() On Error GoTo abc: Dim cBut As CommandBarControl On Error Resume Next Application.CommandBars("Cell").Controls("Browse Workbooks").Delete Set cBut = Application.CommandBars("Cell").Controls.Add(Type:=msoControlPopup, Temporary:=True) cBut.Caption = "Browse Workbooks" cBut.OnAction = "add_controls_my_menu" abc: Exit Sub End Sub Sub add_controls_my_menu() 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("Browse Workbooks").Controls On Error Resume Next cmda.Delete Next For Each wk In Application.Workbooks Set cbut2 = Application.CommandBars("Cell").Controls("Browse Workbooks").Controls.Add(Type:=msoControlPopup) With cbut2 .Caption = wk.Name .OnAction = "my_menu_activate_workbook" End With For Each wks In wk.Sheets If wks.Visible = xlSheetVisible Then Set CBT3 = cbut2.Controls.Add(Type:=msoControlButton) With CBT3 .Caption = wks.Name .OnAction = "my_menu_activate_WORKSHEET" End With End If Next Next End Sub Sub my_menu_activate_workbook() On Error Resume Next Windows(Application.CommandBars.ActionControl.Caption).Activate End Sub Sub my_menu_activate_WORKSHEET() On Error Resume Next Sheets(Application.CommandBars.ActionControl.Caption).Activate End Sub

How to use:

  1. 1 Open a new workbook
  2. 2 Press ALT+ F11
  3. 3 Click on Thisworkbook module and paste workbook module code
  4. 4 Right Click on Thisworkbook module and choose -> Insert -> module
  5. 5 Now paste module1 or in any new module code in it.
 

Test the code:

  1. Please download the working file below
 

Sample File:

Add open workbooks menu.zip 11.68KB 

Approved by Jacob Hilderbrand


This entry has been viewed 398 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express