Results 1 to 3 of 3

Thread: Navigation Pane

  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Newfoundland
    Posts
    41
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Navigation Pane

    I have a spreadsheet with multiple reports on each sheet. Each report has its own range name. (I use different sheets for different service areas.) Within each sheet, is it possible to have some sort of navigation pane which lists the range names (reports) for the worksheet. This would be similar to the functionality of Excel

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Navigation Pane

    What approach you take really depends on the size of each report, i.e. does the entire report fit (or almost fit) on one screen. If so, and you have enough space to freeze and area to hold you navigation buttons, you should have little difficulty. The fact that each report has its own name makes it easy to set navigation buttons. You could record a simple macro for each report which simply takes you to that range. You then assign the macro to Button, or any object. Repeat for each report. Even if each report does not fit on the screen you could have a button located at the end of the report which brings you back to the main navigation area.

    To record a macro to go to a range, go to Tools|Macro|Record New Macro, and give it a name like GotoReportA. Then when that is done, press F5 (or Ctrl-G), select the range click OK, and then perhaps click at the first cell in the report. Stop the macro recorder. Create a button or other object and by right clicking on it, you assign your Macro. Then include whatever text you require on the button. Do the same for range and then palce them together in a frozen pane at the top or to right of the screen.

    If your reports are many and large then the use of VBA might be best.

    Hope the above gets you started,

    Andrew C

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Navigation Pane

    Hi,
    I do something vaguely related to navigate workbooks with a lot of sheets in them. I have a userform with a listbox on it listing all sheets and double-clicking on a name activates that sheet. I then added a simple Userform.Show macro to the shortcut menu you get when right-clicking a cell.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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