Adding Navigation Menu

Topic

New Reply

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

Reply | Quote

Replies

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 earlier Thread which was looking to have Excel behave like a browser, and may require some amendments.

Andrew C

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote