Consulting

Results 1 to 5 of 5

Thread: Navigation menu to only show visible tabs

  1. #1

    Navigation menu to only show visible tabs

    I found this excellent code to create a navigation menu. The only problem is that is shows all the tabs, including the hidden ones. Can someone help me modify it to only show the visible tabs?

    Thank you.

    [vba]
    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 = "&Navigate"
    '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("&Navigate").Delete
    On Error GoTo 0
    End Sub
    [/vba]

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,149
    Location
    [vba]

    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 = "&Navigate"
    '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
    If Sh.Visible = xlSheetVisible Then
    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
    End If
    Next Sh
    End Sub
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    Outstanding, thank you very much.

  4. #4
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    3,837
    Location
    Are you going to use this or the button event in your other thread?
    Remember To Do the Following....
    Use tags when posting code to the thread,
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  5. #5
    This is just a navigation menu that shows up at the top alongside the File, Edit, View, etc... menu. This is different than the other one.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •