Hide Worksheets
Posted by Richard Spilky on January 13, 2001 7:26 PM
Let's say I have 21 worksheets in a workbook. The first worksheet is called "INPUT" and the rest are named "ONE", "TWO", "THREE", respectively up to "TWENTY". Depending upon a certain selection in a certain cell on the "INPUT" worksheet, say, 1, 2, 3, or 4 for example, I would like certain of those worksheets to be hidden or visable depending on which number is selected in this particular cell on the "INPUT" worksheet. For example, if "1" is selected, than worksheets ONE through FIVE would be visible and worksheets SIX through TWENTY would become hidden. If "2" is selected in this cell, than worksheets SIX through TEN would become visible and all the rest, (ONE through FIVE, and ELEVEN through TWENTY would become hidden, and so on.
Can this be done?
Richard Spilky
655 Bent Ridge Lane
Barrington, IL 60010
spilky@speakeasy.net
or
rspilky@newenergy.com
Posted by Dave Hawley on January 14, 2001 12:36 AM
Hi Richard
Right click on your worksheet name tab and select "View Code" then paste the code below over the top of what you see.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Sht As Worksheet, i As Integer
Dim ii As Integer
If Target.Cells.Count <> 1 Then Exit Sub
If Target.Address = "$A$1" Then
Application.ScreenUpdating = False
Application.EnableEvents = False
Select Case Target.Value
Case 1
For i = 1 To 5
For ii = 6 To 11
Sheets(i).Visible = False
Sheets(ii).Visible = True
Next ii
Next i
Case 2
For i = 6 To 11
For ii = 12 To 17
Sheets(i).Visible = False
Sheets(ii).Visible = True
Next ii
Next i
End Select
Application.ScreenUpdating = True
Application.EnableEvents = True
End If
End Sub
This code will hide and unhide sheets based on their Index number with 1 being the first sheet on the left. You can simply add more Case statements and change the numbers to suit. The number in cell A1 is the trigger for this example.
Hope this helps
Dave
- OzGrid Business Applications
