Results 1 to 5 of 5

Thread: Trying to create a worksheet navigator...
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Oct 2010
    Posts
    325
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Trying to create a worksheet navigator...

    Hello,

    I have developed a very long worksheet, with many sections that can be collapsed and expanded with outlining.

    I had an idea to add a "worksheet navigator" section at the top of the page, and maybe a button or dropdown menu within each section, to make it easy for the user to quickly jump to other sections. Does anyone have any good tips or ideas, maybe even some VBA code to do this?

    Also, what is the VBA code to expand and collapse individual sections that have been outlined? I tried using the macro recorder, but it doesn't recognize when individual sections are collapsed or expanded...

    Thanks for any help!

    Best regards,
    Kelvin

  2. #2
    Board Regular
    Join Date
    Nov 2008
    Location
    Netherlands
    Posts
    3,471
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Trying to create a worksheet navigator...

    Code:
    Sub testoutl()
        Dim v
        
        With ActiveSheet
        
        v = .Range("A10:A18").EntireRow.OutlineLevel
        'collapse
         .Outline.ShowLevels rowLevels:=1
        MsgBox v
        'open outline
         .Outline.ShowLevels rowLevels:=v
         
        End With
    
    
    
    End Sub
    This will check the outline level for the rows A10:A18

    level 1 is no outline, level 2 is 1st outlinelevel

  3. #3
    Board Regular nuked's Avatar
    Join Date
    Mar 2013
    Location
    London, UK
    Posts
    883
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Trying to create a worksheet navigator...

    You could insert some hyperlinks at the top of your sheet and freeze the panes so that the 'menu' is available throughout.
    The hyperlinks wizard will let you identify places within the sheet to move to, or you could make use of named-ranges

  4. #4
    Board Regular
    Join Date
    Nov 2008
    Location
    Netherlands
    Posts
    3,471
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Trying to create a worksheet navigator...

    A bit more playing, difficult to find everything in oone place:
    Code:
    Sub testoutl2()
        Dim v
        ' v = VGetOutp([e5], [c7:c10], 1)
        Dim wsWS As Worksheet
        
        With ActiveSheet
        
            v = .Range("A10:A18").EntireRow.OutlineLevel
    '        MsgBox v
            If .Range("A19").EntireRow.ShowDetail Then
                 'collapse
     '            .Outline.ShowLevels rowLevels:=1
                 .Range("A19").EntireRow.ShowDetail = False
                 Else
                'open outline
     '            .Outline.ShowLevels rowLevels:=v
                 .Range("A19").EntireRow.ShowDetail = True
            End If
        End With
    End Sub
    Where my previous code set the outline for the whole sheet (ie all sections open or collapse) the one above does it on a section basis. For this to work, the range where .Showdetail is used is the summary row, so the row underneath the collapsed rows (the one with the + or - next to it)

  5. #5
    Board Regular
    Join Date
    Oct 2010
    Posts
    325
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Trying to create a worksheet navigator...

    Thanks, I didn't know that ShowLevels could be applied to individual rows and columns. Very helpful!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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