Trying to create a worksheet navigator...

Kelvin Stott

Active Member
Joined
Oct 26, 2010
Messages
338
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
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
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
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,564
Messages
6,114,334
Members
448,567
Latest member
Kuldeep90

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top