Trying to create a worksheet navigator...

[Kelvin Stott]

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

 

[sijpie]

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

 

[nuked]

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

 

[sijpie]

A bit more playing, difficult to find everything in oone place:

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)

 

[Kelvin Stott]

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