Dynamic drop down in custom ribbon tab

[theprincipal78]

hi all

I have a dynamic sheet navigator installed as an Add-In.

apart from that I have custom tabs and buttons set in place with the office custom ui editor.

I would like the dynamic sheet navigator to appear within the custom tabs which are in place as oppose to appear in a separate "Add-In"-Tab.

see the coding below:

Standard Module
Option Explicit
Option Private Module 'prevent menu macros appearing under Tools|Macros

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

If Sh.Visible = True Then

Set SubMenuItem = MenuItem.Controls.Add(Type:=msoControlButton)
SubMenuItem.Caption = Sh.Name
SubMenuItem.OnAction = "'LinkSheet(" & i & ")'"
If ActiveSheet.Name = Sh.Name Then SubMenuItem.FaceId = 1087

End If

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



Workbook Module
Option Explicit

Private Sub Workbook_Activate()
CreateMenu
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
DeleteMenu
ThisWorkbook.Save
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

Part of Custom UI Editor

<!-- This is example : Custom tab for your favorite macros Part 2 -->


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

<!-- Add Custom tab to the ribbon with your favorite buttons-->
<!-- The example add five groups to the new tab -->
<!-- On the third group there is a menu with five options-->
<!-- On the fourth group there is a splitbutton example-->
<!-- On the fifth group there are button groups (the last one with built-in buttons)-->

<ribbon>
<tabs>
<tab id="MyCustomTab" insertBeforeMso="TabInsert" label="BBG Equity Portfolio">

<group id="customGroup1" label="Navigate Workbook">
<button id="customButton4" size="large" label="Table Of Content" imageMso="BlogHomePage" onAction="Macro4">
<button id="customButton2" size="large" label="Previous Sheet" imageMso="CatalogMergeGoToPreviousRecord" onAction="Macro2">
<button id="customButton3" size="large" label="Next Sheet" imageMso="CatalogMergeGoToNextRecord" onAction="Macro3">
<button id="customButton1" size="large" label="Sheet List" onAction="Macro1" image="SheetList">




<menu id="Menu13" label="Menu with Sub Menus">
</menu><button id="Button18" label="Button18">
<menu id="Menu14" label="Menu14">
</menu><button id="Button19" label="Button19">
<button id="Button20" label="Button20">
<menu id="Menu15" label="Menu15">
</menu><button id="Button21" label="Button21">
<button id="Button22" label="Button22">
<menu id="Menu16" label="Menu16">
</menu><button id="Button23" label="Button23">
<button id="Button24" label="Button24">



<button id="Button25" label="Button25">





</button></group>


appreciate any help.
thanks</tab></tabs></ribbon></customui>