Collapsing Index and Hide/Unhide Sheets

[NeoGen]

Hi all,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I’m new to using macro’s in excel so not fully up to speed with all aspect of them, but I will try to explain what I would like to achieve as best as I can and hopefully one of the many Excel experts on here will be able to solve all my woes
<o></o>
I have over 60 sheets in the workbook and this question may get a bit complicated so I think it would be best to split it up in to manageable questions, so here goes:<o></o>
<o></o>
I Have a Workbook with around 60+ sheets I have successfully used this example: http://www.mrexcel.com/forum/showthread.php?t=344144 to hide all the sheets except the ‘Index’ one, I now need to try to achieve the following:<o></o>
<o></o>

<o></o>
Part 1 – would like to create a collapsing index structure utilising buttons on the index page that are links to other sheets within the workbook (is this done by hyperlinks or another method?) as if I use hyperlinks I have found they do not work if the sheets are hidden (which I need them to be). The collapsing index would look something like this: <o></o>
<o></o>

Temp 1 <o></o>

4000 Temp 1A <o></o>

4001 Temp 1AA<o></o>
4002 Temp 1AB<o></o>
4003 Temp 1AC<o></o>
4004 Temp 1AD<o></o>
4005 Temp 1AE<o></o>
4006 Temp 1AF<o></o>
​

4100 Temp 1B <o></o>

4101 Temp 1BA<o></o>
4102 Temp 1BB<o></o>
4103 Temp 1BC<o></o>
4104 Temp 1BD<o></o>
​

Temp 1E<o></o>
Temp 1F<o></o>
Temp 1G<o></o>
​

Temp 2<o></o>

Temp 2A<o></o>
Temp 2B<o></o>
Temp 2C<o></o>
Temp 2D<o></o>
​

<o></o>
Temp 3<o></o>
Temp 4<o></o>
Temp 5<o></o>
Temp 6<o></o>
Temp 7<o></o>
Temp 8<o></o>
Temp 9<o></o>
Temp 10
<o></o>
<o></o>
etc
<o></o>
<o></o>
So when Temp 1 is clicked on it expands showing 4000 Temp 1A, 4100 Temp 1B etc and then when one of them is clicked on they expand to show 4001 Temp 1AA etc<o></o>
<o></o>
One problem is that some of the sheets have been given codes instead of names, e.g.
<o></o>
<o></o>
4001, 4002, 4003, 4004, 4005, 4006<o></o>
4101, 4102, 4103<o></o>
4201, 4202, 4203, 4204, 4205<o></o>
<o></o>

Etc.<o></o>
<o></o>

Will this throw a spanner in the works when using macros and VB<o></o>
<o></o>

<o></o>
Part 2 – From the index page I want everything to be hidden until the link to that page is clicked on; I have not been able to achieve this with hyperlinks as they need the page to be visible? I have tried to use the other code on the above example, but without success, it seems I can only navigate to a sheet if it is unhidden; <o></o>
<o></o>

<o></o>
Part 3 – I need a quick and painless way to get back to the index page hiding any visible pages again, I only want to have the sheet that is being worked on visible at anytime all other sheets to be hidden.<o></o>
<o></o>

Many thanks for any input.<o></o>
<o></o>

Thanks<o></o>
<o></o>

Nick<o></o>

 

[GettingThere]

Hey Nick,

1. For a collapsing structure like this I think it would probably be easier to use the grouping options (Data - Group and Outline - Group) - This can go down to several layers so would be fine for your needs here and is much less time consuming than indexing hundreds of hyperlinks/command buttons etc.

2. As far as the hyperlink/hidden sheets alternative - are the other sheets named after the link you want to click on? E.g. you click on "4001 Temp 1AA" you want it to take you to the sheet called "4001 Temp 1AA"

If so - this should help:

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    On Error Resume Next
        Sheets(ActiveCell.Value).Visible = True
        Sheets(ActiveCell.Value).Select
        Cancel = True
    On Error GoTo 0
End Sub

- This needs to go in ThisWorkbook - If this isn't how your workbook is set out we'll have to come up with something else...

3. As for getting back to the index sheet - go onto one of the other sheets and start recording a macro. I'd suggest giving it a short cut key e.g. Ctrl&Shift&I(ndex), then return to the Index sheet and stop recording. When you go in to the VBE and find the macro you've just recorded it will show something like Sheets("Index").select - Just underneath that type the name of the macro you use to hide all the sheets - ending up with something like:

Sub Go_Home()
'
' Go_Home Macro
' Macro recorded 13/03/2009 by Your Mum
'
' Keyboard Shortcut: Ctrl+Shift+I
'
    Sheets("Sheet1").Select
    Hide_All_Sheets
End Sub

Then you're set to go - you can go to whatever sheet you want by double clicking on the click on the index sheet then press Ctrl+Shift+I at any time to return to the index sheet and hide all the others (This macro could easily be attached to a command button as well but then it would have to be copied to each sheet...)

Hope that's is of some help - If there's anything else please give me a shout.

Cheers,

A.

 

[NeoGen]

Many thank for that, just an update based on advice you gave:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>

1. For a collapsing structure like this I think it would probably be easier to use the grouping options (Data - Group and Outline - Group) - This can go down to several layers so would be fine for your needs here and is much less time consuming than indexing hundreds of hyperlinks/command buttons etc.

<o></o>
That’s exactly what I was looking for, the only advancement on it would be to somehow get them to expand when clicking on a link instead of the +/- on the left hand side as the last layer would be links to other hidden sheets, the workbook may be used by people not to familiar with the expanding section on the left, so what I would like if possible would be: If I click on Temp 1 then it would expand showing:<o></o>
<o></o>
4000 Temp 1A <o></o>
4100 Temp 1B<o></o>
<o></o>
Etc, then if I clicked on 4000 Temp 1A it would expand showing:<o></o>
<o></o>
4001 Temp 1AA
4002 Temp 1AB
4003 Temp 1AC
4004 Temp 1AD
4005 Temp 1AE
4006 Temp 1AF<o></o>
<o></o>
I also need to figure out how to get it to show the hidden sheets when one of the links is clicked on e.g. if 4006 Temp 1AF is clicked on it needs to unhide and go to sheet 4006, but if I use a hyperlink to do this nothing happens as 4006 is hidden??<o></o>
<o></o>

2. As far as the hyperlink/hidden sheets alternatives - are the other sheets named after the link you want to click on? E.g. you click on "4001 Temp 1AA" you want it to take you to the sheet called "4001 Temp 1AA"<o></o>

If so - this should help:<o></o>
<o></o>

<o:p></o:p>

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)<o:p></o:p>
   On Error Resume Next<o:p></o:p>
       Sheets(ActiveCell.Value).Visible = True<o:p></o:p>
       Sheets(ActiveCell.Value).Select<o:p></o:p>
       Cancel = True<o:p></o:p>
   On Error GoTo 0<o:p></o:p>
End Sub<o:p></o:p>

<o></o>
<o></o>
- This needs to go in ThisWorkbook - If this isn't how your workbook is set out we'll have to come up with something else...

<o></o>
<o></o>
The sheets are named after the code and not the name, e.g the sheets are 4001, 4002, 4003, 4004, 4101, 4102 etc etc, the names are just on the index, the sheets can be named using both, just wanted to keep tab sizes to a minimum,<o></o>
plus the codes are the important part<o></o>
<o></o>

3. As for getting back to the index sheet - go onto one of the other sheets and start recording a macro. I'd suggest giving it a short cut key e.g. Ctrl&Shift&I(ndex), then return to the Index sheet and stop recording. When you go in to the VBE and find the macro you've just recorded it will show something like Sheets("Index").select - Just underneath that type the name of the macro you use to hide all the sheets - ending up with something like: <o></o>

<o></o>

<o:p></o:p>

Sub Go_Home()<o:p></o:p>
'<o:p></o:p>
' Go_Home Macro<o:p></o:p>
' Macro recorded 13/03/2009 by Your Mum<o:p></o:p>
'<o:p></o:p>
' Keyboard Shortcut: Ctrl+Shift+I<o:p></o:p>
'<o:p></o:p>
   Sheets("Sheet1").Select<o:p></o:p>
   Hide_All_Sheets<o:p></o:p>
End Sub<o:p></o:p>

<o></o>
<o></o>
Then you're set to go - you can go to whatever sheet you want by double clicking on the click on the index sheet then press Ctrl+Shift+I at any time to return to the index sheet and hide all the others (This macro could easily be attached to a command button as well but then it would have to be copied to each sheet...)

<o></o>
<o></o>
As other users will be using it, I think this one would be best left to clicking on the index tab itself, which in turn would revert back to the index sheet being visible. <o></o>
<o></o>
<o></o>
Many thanks for all your help, it’s definitely moving me in the right direction, I hope that the above is useful in outlining what I’m trying to achieve.<o></o>
<o></o>
Regards<o></o>
<o></o>
Nick
<o></o>