I have a spreadsheet around 6000 rows long. I want to include a means of
navigating around the spreadsheet in a freeze framed section at the top. I
have considered a number of options including a simple drop down list (via
the validaton command) and a goto button in VBA or the use of a dropdown list
and hyperlinks.

While this works reasonably well there are arond 100 locations I wish to
navigate to and including these in a single drop-down list is not practical -
I really want to be able to have some sort of drop down list which displays
10 high level categories, then within each of these categories a further 10
sub categories, much like standard office menus. Does anyone have any useful
pointers or VBA to help me achieve this

cheers

Simon

You can find some info here about dependent drop down lists, and use that as
a starting place. Make the first list your 10 hi-level categories &
subsequent lists will depend on the first choice made

http://www.contextures.com/xlDataVal02.html


"Simon" wrote:

I have a spreadsheet around 6000 rows long. I want to include a means of
navigating around the spreadsheet in a freeze framed section at the top. I
have considered a number of options including a simple drop down list (via
the validaton command) and a goto button in VBA or the use of a dropdown list
and hyperlinks.

While this works reasonably well there are arond 100 locations I wish to
navigate to and including these in a single drop-down list is not practical -
I really want to be able to have some sort of drop down list which displays
10 high level categories, then within each of these categories a further 10
sub categories, much like standard office menus. Does anyone have any useful
pointers or VBA to help me achieve this

cheers

Simon

Duke

Thanks very much indeed for that - just what I needed. On a related point I
have used grouping within the 6,000 line spreadsheet to hide rows that I
don't need to look at. Do you know if there is anyway of ungrouping all rows
in the spreadsheet via VBA so that the user can see everything. When I
record a macro doing this in Excel 2002 it is recorded as an Excel4macro with
reference made to the specific lines in the spreadsheet which are been
unhidden, i.e. there is no ungroup.all command or equivalent.

Simon

"Duke Carey" wrote:

You can find some info here about dependent drop down lists, and use that as
a starting place. Make the first list your 10 hi-level categories &
subsequent lists will depend on the first choice made

http://www.contextures.com/xlDataVal02.html


"Simon" wrote:

I have a spreadsheet around 6000 rows long. I want to include a means of
navigating around the spreadsheet in a freeze framed section at the top. I
have considered a number of options including a simple drop down list (via
the validaton command) and a goto button in VBA or the use of a dropdown list
and hyperlinks.

While this works reasonably well there are arond 100 locations I wish to
navigate to and including these in a single drop-down list is not practical -
I really want to be able to have some sort of drop down list which displays
10 high level categories, then within each of these categories a further 10
sub categories, much like standard office menus. Does anyone have any useful
pointers or VBA to help me achieve this

cheers

Simon

One line of code will unhide all rows:

Columns("A:A").EntireRow.Hidden = False

"Simon" wrote:

Duke

Thanks very much indeed for that - just what I needed. On a related point I
have used grouping within the 6,000 line spreadsheet to hide rows that I
don't need to look at. Do you know if there is anyway of ungrouping all rows
in the spreadsheet via VBA so that the user can see everything. When I
record a macro doing this in Excel 2002 it is recorded as an Excel4macro with
reference made to the specific lines in the spreadsheet which are been
unhidden, i.e. there is no ungroup.all command or equivalent.

Simon

"Duke Carey" wrote:

You can find some info here about dependent drop down lists, and use that as
a starting place. Make the first list your 10 hi-level categories &
subsequent lists will depend on the first choice made

http://www.contextures.com/xlDataVal02.html


"Simon" wrote:

I have a spreadsheet around 6000 rows long. I want to include a means of
navigating around the spreadsheet in a freeze framed section at the top. I
have considered a number of options including a simple drop down list (via
the validaton command) and a goto button in VBA or the use of a dropdown list
and hyperlinks.

While this works reasonably well there are arond 100 locations I wish to
navigate to and including these in a single drop-down list is not practical -
I really want to be able to have some sort of drop down list which displays
10 high level categories, then within each of these categories a further 10
sub categories, much like standard office menus. Does anyone have any useful
pointers or VBA to help me achieve this

cheers

Simon

Hi Simon,

How about a custom toolbar with a menu tree? Obviously not what you
were after in terms of the frozen panes, but this would 'follow you
around the worksheet' as it were if you din't have it docked?

Cheers,
JF.

"?B?U2ltb24=?=" wrote in message ...
I have a spreadsheet around 6000 rows long. I want to include a means of
navigating around the spreadsheet in a freeze framed section at the top. I
have considered a number of options including a simple drop down list (via
the validaton command) and a goto button in VBA or the use of a dropdown list
and hyperlinks.

While this works reasonably well there are arond 100 locations I wish to
navigate to and including these in a single drop-down list is not practical -
I really want to be able to have some sort of drop down list which displays
10 high level categories, then within each of these categories a further 10
sub categories, much like standard office menus. Does anyone have any useful
pointers or VBA to help me achieve this

cheers

Simon