How to improve visuality?
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 |
How to improve visuality?
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 |
How to improve visuality?
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 |
How to improve visuality?
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 |
How to improve visuality?
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 |
How to improve visuality?
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 |
How to improve visuality?
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 |
All times are GMT +1. The time now is 12:21 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com