Results 1 to 6 of 6
  1. #1
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    Location
    New York, New York, USA
    Posts
    768
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Adding Navigation Menu

    G'Day All,

    I have a spreadsheet with several sheets on it. This spreadsheet will be used by management - so I want to control what is shown and the navigation. There are several optional ways of doing this and I wanted views on which is better / easier or an alternate method.

    The basic idea was something like the new layout of the open dialog box with the 5 big buttons down the left controlling what is shown on the right.

    Methods I've thought of ...

    1. Split the screen and have the left hand sheet with a series of buttons on it that change what is shown on the right hand screen. (problem - couldn't get excel 2000 to unsync the sheets)

    2. Have a tool bar as a navigation bar (problem - tool bar buttons too small).

    3. Create a dialog box / form than controls navigation (problem - will have to cope with the form getting in the way of the information displayed)

    Any other options greatly accepted.

    Cheers,

    Tim
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

  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: Adding Navigation Menu

    Tim,

    I think you first option was probably best, but don't understand what you mean about unsyncing the sheets.

    If the workbook is shown in two windows, the left hand window could just contain buttons that determine the content of the right hand window. I attached a file to show what I mean. This is based on an <A target="_blank" HREF=http://link>earlier Thread</A> which was looking to have Excel behave like a browser, and may require some amendments.

    Andrew C
    Attached Files Attached Files

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding Navigation Menu

    A form seems to me the most appropriate choice. You can use an event to trigger the form hidden/unhidden. e.g. by a click on a button or a cell

  4. #4
    zenood
    Guest

    Re: Adding Navigation Menu

    Andrew's workbook seems to be what you want.
    It works like a dream ! My compliments!

    I'll have to digest the code slowly, though <img src=/S/grin.gif border=0 alt=grin width=15 height=15>


    However, if editing the code is not for you, a user-form would work too.
    If you're using Excel 2000 or higher, you could display a modeless UserForm with this code
    <pre> UserForm1.Show vbModeless
    </pre>

    A modeless userform allows the user to access other objects on the worksheet even while the the userform is active.
    I mean, the user can scroll around the sheet, etc...

    Some suggestions on getting the userform out of the way
    when someone has clicked on a button to view a particular sheet,

    1. You can reduce the userform height/width/position to get it out of the way.
    You'll need a button to restore the userform to 'full-view' when another sheet is to be previewed

    or

    2. Hide the first userform, show another one with just a single button to re-display the first one (and hide the second one)

    Hope this helps
    Ciao

  5. #5
    Star Lounger
    Join Date
    Jan 2001
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding Navigation Menu

    Timbo,
    How about using hyperlinks (from images, text, whatever you want the buttons to be) to the different parts of the workbook. To follow the webpage theme further you could put back buttons to return to the navigation or include the navigation buttons on each sheet.

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

    Re: Adding Navigation Menu

    Actually much of the code is not strictly necessary unless you need to disable closing the the windows other than by File Exit ( a requirement of the original requestor ).

    I did inadvertently remove the following from the ThisWorkbook module, which may cause problems if not replaced :

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    RestoreSystemMenu
    End Sub

    I attach a simplified version with the same functionality, but without any API calls.

    Andrew
    Attached Files Attached Files

Posting Permissions

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