Hide/Unhide worksheet by macro (code?) depending on link cho

[LoriD]

I have a workbook with several sheets in it. I have created a Table of Contents where I put a hyperlink to each of the sheets. I'd like to hide the sheets, except for the TOC. Then when a user selects the link to Sheet1, Sheet1 is unhidden.
If the user goes back to the TOC and selects the link to Sheet3, Sheet3 becomes unhidden, and whichever other sheet that is open gets hidden. (Except for the TOC)
Can anyone tell me how to do this?

 

[Joe Was]

These macros will toggle your code on or off. The first macro will set the on-off switch. The next macro will automatically run your Un-Hide Sheet code, based upon the toggle value. The last macro will un-hide the sheet if the toggle is set and you run its macro. JSW

Public myKey As Integer
Sub myOnOff()
'This go's into the sheet module for the sheet you want
'it to work on.
'This code switches the macros "On" or "Off".
'This will flip-flop between On & Off each time it is run!
If myKey = 0 Then
myKey = 1
Else
If myKey = 1 Then
myKey = 0
End If
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
'This go's into the sheet module for the sheet you want
'it to work on.
'This is a test macro, to test the on/off switch.
If myKey <> 0 Then myKey = 1
'This is the key to turning the macro on.
If myKey = 1 Then
'This is the code to run, if the macro switch is on
'Un-Hide Sheet3.
Sheets("Sheet3").Visible = True
Else
GoTo myEnd
End If
myEnd:
End Sub

Sub mySW()
'This is a test macro, to test the on/off switch.
If myKey <> 0 Then myKey = 1
'This is the key to turning the macro on.
If myKey = 1 Then
'This is the macro to run, if the macro switch is on.
'Un-Hide Sheet3.
Sheets("Sheet3").Visible = True
Else
GoTo myEnd
End If
myEnd:
End Sub

 

[LoriD]

Thanks Joe, but I'm not sure I understand. Do I put this code in the Table of Contents? Or does it go in every sheet?

Does it run by selecting a link? Or do I have to create a button or key a number?

Thanks again. Lori

 

[Joe Was]

Sorry thought you did code.

Go to "Tools-Macros-Record Macro" then "Format-Hide" for each sheet you want to hide go through the format-hide. Then press the macro recorder stop square. Right click the sheet tab of your one remaining sheet, select "View Code," select modules, dubble-click the module name and your recorded macro code should display. This is whare you can add to or modify your code. At this time we will not be modifying any code.

So, hit the top Right close "X" to return to your sheet, select "Tools-Macros-Macro, select Options" assign a hot key letter to your macro, like: h so when you hit "Ctrl+h" your hide macro will run. Cancel the macro select window.

Now record a new macro, this one will un-hide all your sheets. Just do what we did above only select "UnHide" in place of "Hide."

You now have two macros to hide or unhide your sheets. You can also attach your macros to form buttons from the "View-Toolbars" select forms, drag button to worksheet assign macro. This will give your sheet a button to run your macro from with a mouse click if you want.

To add other functionallity to your recorded code you will need to write or cut and paste additional code to your code.

Hope this helps. JSW

 

[Joe Was]

You will need to remove your hyperlinks from your first sheet. Then write a "Case" macro that looks for what cell is selected, when you click on a item in your list, as an event code. Under the Case code put the UnHide code for the sheet then add the Range code to go to your item.

Then add a "SheetSelect" event macro that hides the sheet when ever a new sheet is selected. JSW

 

[Joe Was]

This message was edited by Joe Was on 2002-09-27 07:18

 

[LoriD]

Thanks, Joe for spending time on this, but you've lost me again with that last piece. I can pretty much record a macro and then edit it!
How do I write a "Case" macro? I've read the support file, but can't see how to edit the code they supply to look at the cell I select.

 

[Joe Was]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myPick As String
'This code go's in the List Sheet, (Sheet1) module.
'Sheet1 is the list sheet, in cells A2:A5
'The case names, like: 1st, 2nd, ...
'are in the Sheet1 List, in cells A2:A5.
'When one of these cells is selected, its case code runs.

myPick = Selection.Value
Select Case myPick

Case "1st"
'Below is the Sheet Name you want to un-hide.
Sheets("Sheet2").Visible = True
'Below are the Sheet Names you want to hide.
Sheets("Sheet3").Visible = False
Sheets("Sheet4").Visible = False
Sheets("Sheet5").Visible = False
'Make the Sheet below active.
Sheets("Sheet2").Select

Case "2nd"
Sheets("Sheet3").Visible = True
Sheets("Sheet4").Visible = False
Sheets("Sheet5").Visible = False
Sheets("Sheet2").Visible = False
Sheets("Sheet3").Select

Case "3rd"
Sheets("Sheet4").Visible = True
Sheets("Sheet5").Visible = False
Sheets("Sheet2").Visible = False
Sheets("Sheet3").Visible = False
Sheets("Sheet4").Select

Case "4th"
Sheets("Sheet5").Visible = True
Sheets("Sheet2").Visible = False
Sheets("Sheet3").Visible = False
Sheets("Sheet4").Visible = False
Sheets("Sheet5").Select

Case Else
'If any other cell selected, do nothing.
Sheets("Sheet2").Visible = False
Sheets("Sheet3").Visible = False
Sheets("Sheet4").Visible = False
Sheets("Sheet5").Visible = False
Sheets("Sheet1").Select
Exit Sub
End Select
End Sub


Hope this helps. JSW
This message was edited by Joe Was on 2002-09-27 20:30