Hi,

I have a WB that included 20 WSs.
I do distribute the WB to many people on a monthly basis and as a general
feedback I got only one "negative" point, that is the possibility to have and
overview on how the 20 WS are structured and how the information included in
each of them is relates to other WSs
I was thinking to have an extra WS where I design the structure and make it
active, meaning that from there they can go directly to the WS and viceversa.

Do you have any alternative suggestion on, either the way to do this or
maybe a total different option I could evaluate?

Thank you
Alex

Hi,
If i understand correctly, it is a navigation problem? If so, you can use
the HYPERLINK() worksheet function to jump from, say, a table-of-contents
sheet to the 20 detail sheets and vise versa, eg:
Say you book is called Revenue.xls
Say you have a sheet Table Of Contents and a sheet Texas
On the sheet table of contents, cell B5, enter the formula:
=HYPERLINK("[Revenue.xls]Texas!A1", "Texas")
-- this shows 'Texas' in A5 and go to sheet Texas was then cell is
clicked.
Now on Sheet Texas, say cell A1, enter the formula:
=HYPERLINK("[Revenue.xls]'Table Of COntents'!A1", "Contents")
-- this shows 'Table of contents' an go to sheet TableOf Contents
when clicked.
You could also make a Previous and Next hyperlinks in each sheet.

I hope this helps,
Sebastien


"Metallo" wrote:

Hi,

I have a WB that included 20 WSs.
I do distribute the WB to many people on a monthly basis and as a general
feedback I got only one "negative" point, that is the possibility to have and
overview on how the 20 WS are structured and how the information included in
each of them is relates to other WSs
I was thinking to have an extra WS where I design the structure and make it
active, meaning that from there they can go directly to the WS and viceversa.

Do you have any alternative suggestion on, either the way to do this or
maybe a total different option I could evaluate?

Thank you
Alex

Sebastienm,

Is there a way to place a button (ToC) in the toolbar?
I mean, once the ToC has been created as a WS, do I have a way to make it
visible in the toolbar so that people click on that and see the content?

Thanks
Alex

"sebastienm" wrote:

Hi,
If i understand correctly, it is a navigation problem? If so, you can use
the HYPERLINK() worksheet function to jump from, say, a table-of-contents
sheet to the 20 detail sheets and vise versa, eg:
Say you book is called Revenue.xls
Say you have a sheet Table Of Contents and a sheet Texas
On the sheet table of contents, cell B5, enter the formula:
=HYPERLINK("[Revenue.xls]Texas!A1", "Texas")
-- this shows 'Texas' in A5 and go to sheet Texas was then cell is
clicked.
Now on Sheet Texas, say cell A1, enter the formula:
=HYPERLINK("[Revenue.xls]'Table Of COntents'!A1", "Contents")
-- this shows 'Table of contents' an go to sheet TableOf Contents
when clicked.
You could also make a Previous and Next hyperlinks in each sheet.

I hope this helps,
Sebastien


"Metallo" wrote:

Hi,

I have a WB that included 20 WSs.
I do distribute the WB to many people on a monthly basis and as a general
feedback I got only one "negative" point, that is the possibility to have and
overview on how the 20 WS are structured and how the information included in
each of them is relates to other WSs
I was thinking to have an extra WS where I design the structure and make it
active, meaning that from there they can go directly to the WS and viceversa.

Do you have any alternative suggestion on, either the way to do this or
maybe a total different option I could evaluate?

Thank you
Alex

I have toolbar (commandbar) examples on my website

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Metallo" wrote in message
...
Sebastienm,

Is there a way to place a button (ToC) in the toolbar?
I mean, once the ToC has been created as a WS, do I have a way to make it
visible in the toolbar so that people click on that and see the content?

Thanks
Alex

"sebastienm" wrote:

Hi,
If i understand correctly, it is a navigation problem? If so, you can use
the HYPERLINK() worksheet function to jump from, say, a table-of-contents
sheet to the 20 detail sheets and vise versa, eg:
Say you book is called Revenue.xls
Say you have a sheet Table Of Contents and a sheet Texas
On the sheet table of contents, cell B5, enter the formula:
=HYPERLINK("[Revenue.xls]Texas!A1", "Texas")
-- this shows 'Texas' in A5 and go to sheet Texas was then cell
is
clicked.
Now on Sheet Texas, say cell A1, enter the formula:
=HYPERLINK("[Revenue.xls]'Table Of COntents'!A1", "Contents")
-- this shows 'Table of contents' an go to sheet TableOf
Contents
when clicked.
You could also make a Previous and Next hyperlinks in each sheet.

I hope this helps,
Sebastien


"Metallo" wrote:

Hi,

I have a WB that included 20 WSs.
I do distribute the WB to many people on a monthly basis and as a
general
feedback I got only one "negative" point, that is the possibility to
have and
overview on how the 20 WS are structured and how the information
included in
each of them is relates to other WSs
I was thinking to have an extra WS where I design the structure and
make it
active, meaning that from there they can go directly to the WS and
viceversa.

Do you have any alternative suggestion on, either the way to do this or
maybe a total different option I could evaluate?

Thank you
Alex

Rob,

That's not exactly what I'm looking for.

I am new into VBA threfore my questions are the following

Having more than 20 worksheets in my WB, to improve navigation I wanted to
do the following:

1) Create a WS called ToC
2) Every time that I open my WB, by default it opens the WS "ToC"
3) In the WS "ToC" I will do all the hyperlinks to the other 20 WSs
Question:
4) What is the code I have to write in order to get "ToC" open by default?

Second option (this is also a question):

If I create a complete new toolbar for my WB, can I put in 20 buttons
(links) corresponding to the 20 WS?
The advantage is that the toolbar is always visible and I could put two rows
of 10 buttons each.

Hope my problem is now clearer.

Cheers
Alex


"Rob van Gelder" wrote in message
...
I have toolbar (commandbar) examples on my website

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Metallo" wrote in message
...
Sebastienm,

Is there a way to place a button (ToC) in the toolbar?
I mean, once the ToC has been created as a WS, do I have a way to make it
visible in the toolbar so that people click on that and see the content?

Thanks
Alex

"sebastienm" wrote:

Hi,
If i understand correctly, it is a navigation problem? If so, you can
use
the HYPERLINK() worksheet function to jump from, say, a
table-of-contents
sheet to the 20 detail sheets and vise versa, eg:
Say you book is called Revenue.xls
Say you have a sheet Table Of Contents and a sheet Texas
On the sheet table of contents, cell B5, enter the formula:
=HYPERLINK("[Revenue.xls]Texas!A1", "Texas")
-- this shows 'Texas' in A5 and go to sheet Texas was then
cell is
clicked.
Now on Sheet Texas, say cell A1, enter the formula:
=HYPERLINK("[Revenue.xls]'Table Of COntents'!A1", "Contents")
-- this shows 'Table of contents' an go to sheet TableOf
Contents
when clicked.
You could also make a Previous and Next hyperlinks in each sheet.

I hope this helps,
Sebastien


"Metallo" wrote:

Hi,

I have a WB that included 20 WSs.
I do distribute the WB to many people on a monthly basis and as a
general
feedback I got only one "negative" point, that is the possibility to
have and
overview on how the 20 WS are structured and how the information
included in
each of them is relates to other WSs
I was thinking to have an extra WS where I design the structure and
make it
active, meaning that from there they can go directly to the WS and
viceversa.

Do you have any alternative suggestion on, either the way to do this
or
maybe a total different option I could evaluate?

Thank you
Alex

David McRitchie has some code that will help you build the Table of Contents
worksheet.

http://www.mvps.org/dmcritchie/excel/buildtoc.htm

And if you want to open to that TOC worksheet, put this in a general module:

Option Explicit
sub auto_open()
worksheets("toc").select
'or
'application.goto worksheets("toc").range("a1"), scroll:=true
end sub

=====
An alternate suggestion for #2.
http://groups.google.com/groups?thre...1C74%40msn.com



Metallo wrote:

Rob,

That's not exactly what I'm looking for.

I am new into VBA threfore my questions are the following

Having more than 20 worksheets in my WB, to improve navigation I wanted to
do the following:

1) Create a WS called ToC
2) Every time that I open my WB, by default it opens the WS "ToC"
3) In the WS "ToC" I will do all the hyperlinks to the other 20 WSs
Question:
4) What is the code I have to write in order to get "ToC" open by default?

Second option (this is also a question):

If I create a complete new toolbar for my WB, can I put in 20 buttons
(links) corresponding to the 20 WS?
The advantage is that the toolbar is always visible and I could put two rows
of 10 buttons each.

Hope my problem is now clearer.

Cheers
Alex

"Rob van Gelder" wrote in message
...
I have toolbar (commandbar) examples on my website

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Metallo" wrote in message
...
Sebastienm,

Is there a way to place a button (ToC) in the toolbar?
I mean, once the ToC has been created as a WS, do I have a way to make it
visible in the toolbar so that people click on that and see the content?

Thanks
Alex

"sebastienm" wrote:

Hi,
If i understand correctly, it is a navigation problem? If so, you can
use
the HYPERLINK() worksheet function to jump from, say, a
table-of-contents
sheet to the 20 detail sheets and vise versa, eg:
Say you book is called Revenue.xls
Say you have a sheet Table Of Contents and a sheet Texas
On the sheet table of contents, cell B5, enter the formula:
=HYPERLINK("[Revenue.xls]Texas!A1", "Texas")
-- this shows 'Texas' in A5 and go to sheet Texas was then
cell is
clicked.
Now on Sheet Texas, say cell A1, enter the formula:
=HYPERLINK("[Revenue.xls]'Table Of COntents'!A1", "Contents")
-- this shows 'Table of contents' an go to sheet TableOf
Contents
when clicked.
You could also make a Previous and Next hyperlinks in each sheet.

I hope this helps,
Sebastien


"Metallo" wrote:

Hi,

I have a WB that included 20 WSs.
I do distribute the WB to many people on a monthly basis and as a
general
feedback I got only one "negative" point, that is the possibility to
have and
overview on how the 20 WS are structured and how the information
included in
each of them is relates to other WSs
I was thinking to have an extra WS where I design the structure and
make it
active, meaning that from there they can go directly to the WS and
viceversa.

Do you have any alternative suggestion on, either the way to do this
or
maybe a total different option I could evaluate?

Thank you
Alex




--

Dave Peterson

Q4: Copy this procedure into the Workbook code module.

Private Sub Workbook_Open()
Worksheets("ToC").Activate
End Sub


Second option, you can create two lists using submenus.


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Metallo" wrote in message
...
Rob,

That's not exactly what I'm looking for.

I am new into VBA threfore my questions are the following

Having more than 20 worksheets in my WB, to improve navigation I wanted to
do the following:

1) Create a WS called ToC
2) Every time that I open my WB, by default it opens the WS "ToC"
3) In the WS "ToC" I will do all the hyperlinks to the other 20 WSs
Question:
4) What is the code I have to write in order to get "ToC" open by default?

Second option (this is also a question):

If I create a complete new toolbar for my WB, can I put in 20 buttons
(links) corresponding to the 20 WS?
The advantage is that the toolbar is always visible and I could put two
rows of 10 buttons each.

Hope my problem is now clearer.

Cheers
Alex


"Rob van Gelder" wrote in message
...
I have toolbar (commandbar) examples on my website

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Metallo" wrote in message
...
Sebastienm,

Is there a way to place a button (ToC) in the toolbar?
I mean, once the ToC has been created as a WS, do I have a way to make
it
visible in the toolbar so that people click on that and see the content?

Thanks
Alex

"sebastienm" wrote:

Hi,
If i understand correctly, it is a navigation problem? If so, you can
use
the HYPERLINK() worksheet function to jump from, say, a
table-of-contents
sheet to the 20 detail sheets and vise versa, eg:
Say you book is called Revenue.xls
Say you have a sheet Table Of Contents and a sheet Texas
On the sheet table of contents, cell B5, enter the formula:
=HYPERLINK("[Revenue.xls]Texas!A1", "Texas")
-- this shows 'Texas' in A5 and go to sheet Texas was then
cell is
clicked.
Now on Sheet Texas, say cell A1, enter the formula:
=HYPERLINK("[Revenue.xls]'Table Of COntents'!A1", "Contents")
-- this shows 'Table of contents' an go to sheet TableOf
Contents
when clicked.
You could also make a Previous and Next hyperlinks in each sheet.

I hope this helps,
Sebastien


"Metallo" wrote:

Hi,

I have a WB that included 20 WSs.
I do distribute the WB to many people on a monthly basis and as a
general
feedback I got only one "negative" point, that is the possibility to
have and
overview on how the 20 WS are structured and how the information
included in
each of them is relates to other WSs
I was thinking to have an extra WS where I design the structure and
make it
active, meaning that from there they can go directly to the WS and
viceversa.

Do you have any alternative suggestion on, either the way to do this
or
maybe a total different option I could evaluate?

Thank you
Alex