Custom UI Editor - Worksheet Navigation - Sheet Index - Custom Ribbon

[theprincipal78]

HI all

Just started out with custom ribbons and buttons.

here is my problem:
got a large workbook and trying to figure out how to navigate 80+ sheets best.
I have some custom ribbons in place and would like to assign some specific job to one of the buttons.

so far I have:
1. the ribbons/custom UI Editor in place
2. some workbook events in place
3. subroutines not so much in place

my problem is that the below coding (including worksheet events and subroutines)
creates a separate tab with a menu object and a menu item "Go To Sheet".
Since I already have tabs, groups and a button in place called "Go to Sheet" i would like to avoid the adding part of the code.


any help is highly appreciated.



Custom UI Editor
<!-- This is example : Custom tab for your favorite macros Part 2 -->
<customui xmlns="http://schemas.microsoft.com/office/2006/01/customui"><ribbon>
</ribbon>
</customui>

Workbook Events

Option Explicit

Private Sub Workbook_Activate()
CreateMenu
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
DeleteMenu
End Sub

Private Sub Workbook_Deactivate()
DeleteMenu
End Sub

Private Sub Workbook_Open()
CreateMenu
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
DeleteMenu
CreateMenu
End Sub



Modules

Option Explicit

Sub CreateMenu()
Dim MenuObject As CommandBarPopup, MenuItem As Object
Dim SubMenuItem As CommandBarButton, Sh As Worksheet, i As Long

' Make sure the menus aren't duplicated
Call DeleteMenu

' Add the top-level menu to the Worksheet CommandBar
Set MenuObject = Application.CommandBars(1). _
Controls.Add(Type:=msoControlPopup, Temporary:=True)
'Name of top level menu. Remember to also change caption in DeleteMenu macro
MenuObject.Caption = "&My Menu"

'Add 1st menu item
Set MenuItem = MenuObject.Controls.Add(Type:=msoControlPopup)
MenuItem.Caption = "Go To Sheet"

'Add sub menu items to 1st menu
For Each Sh In ThisWorkbook.Sheets
i = i + 1
Set SubMenuItem = MenuItem.Controls.Add(Type:=msoControlButton)
SubMenuItem.Caption = Sh.Name
SubMenuItem.OnAction = "'LinkSheet(" & i & ")'"
If ActiveSheet.Name = Sh.Name Then SubMenuItem.FaceId = 1087
Next Sh

End Sub

Sub LinkSheet(ShtName As Integer)
If IsMissing(ShtName) Then Exit Sub

On Error Resume Next
Sheets(ShtName).Select
Range("A1").Select
On Error GoTo 0
End Sub

Sub DeleteMenu()
' This sub should be executed when the workbook is closed
' Deletes the Menus

On Error Resume Next
'Change &My Menu to the menu name you want
Application.CommandBars(1).Controls("&My Menu").Delete
On Error GoTo 0
End Sub

 

[theprincipal78]

<customui xmlns="http://schemas.microsoft.com/office/2006/01/customui">







<ribbon>
<tabs>
<tab id="MyCustomTab" insertAfterMso="TabHome" label="My Bloomberg Monitor">
</tab>
</tabs>
</ribbon>

</customui>

 

[theprincipal78]

<customui xmlns="http://schemas.microsoft.com/office/2006/01/customui"></customui>

 

[theprincipal78]

Custom UI Editor

ribbon
tabs
tab id="MyCustomTab" label="My Bloomberg Monitor" insertAfterMso="TabHome"

menu id="MyDropdownMenu" label="My Menu" size="large" imageMso="CodeFindMenu"
button id="customButton3" label="Go To Sheet" onAction="Macro3" imageMso="CodeFindMenu"
button id="customButton4" label="Caption 4" onAction="Macro4" imageMso="CodeFindMenu"
button id="customButton5" label="Caption 5" onAction="Macro5" imageMso="CodeFindMenu"
button id="customButton6" label="Caption 6" onAction="Macro6" imageMso="CodeFindMenu"
button id="customButton7" label="Caption 7" onAction="Macro7" imageMso="CodeFindMenu"
menu

group

tab
tabs
ribbon