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

LoriD

Board Regular
Joined
Apr 1, 2002
Messages
141
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?
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
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

Board Regular
Joined
Apr 1, 2002
Messages
141
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

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
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

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539

ADVERTISEMENT

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
 

LoriD

Board Regular
Joined
Apr 1, 2002
Messages
141
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

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,604
Messages
5,597,128
Members
414,126
Latest member
jellevansoelen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top