Hide sheets?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a way to conditionally hide sheets? Say I have sheets named 1,2,3, and 4. Is there a way to link a condition that if a10 on sheet 1 = "0", then sheet 2 will be hidden (and will remain hidden until something is put into a10?) Thanks for any help!
 
JP,

A Worksheet change event macro could do it. You want to work with a macro?
we'll write you one.
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

JP said:
Is there a way to conditionally hide sheets? Say I have sheets named
Click to expand...
1,2,3, and 4. Is there a way to link a condition that if a10 on sheet 1 =
"0", then sheet 2 will be hidden (and will remain hidden until something is
put into a10?) Thanks for any help!
 
Hi John

Rightclick the sheet tab 1, choose "view code", paste this in:

Private Sub Worksheet_Change(ByVal Target As Range)
With Target(1)
If .Address = "$A$10" Then
Select Case .Value
Case "", 0
If Sheets(2).Visible = True Then _
Sheets(2).Visible = False
Case Else
If Sheets(2).Visible <> True Then _
Sheets(2).Visible = True
End Select
End If
End With
End Sub
 
Hi again. It worked but only for the 1st sheet. How would I do that for more than one sheet (like 18 actually?)?

One last thing- it hides the 2nd sheet when I put a "0" in the column, but does the sheet show back up if the corresponding cell has a value put into it?? Thanks for all of the help!

John
 
Does this mean you want 1 cell to control 18 sheets or do you need 18 cells (one
for each sheet)?

I'm guessing the first:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim wks As Worksheet
With Target(1)
If .Address = "$A$10" Then
For Each wks In Me.Parent.Worksheets
If wks.Name = Me.Name Then
'do nothing
Else
wks.Visible = CBool(Target.Value = 0)
End If
Next wks
End If
End With
End Sub

(This actually does all the other worksheets in the workbook--not the first 18.)

And you should test the code to see if it behaves the way you want.
 
Yeah I need 1 cell for each sheet. Here are the cells that relate to each other:

E11 = Room 1
E13 = Room 2
E15 = Room 3
E17 = Room 4
E19 = Room 5
E21 = Room 6
E23 = Room 7 (and so on down)

I need that if e11, e13, e15, e17, or so on is "0", the corresponding sheet will be hidden until something is put into the cell.

Thanks for any help!

john
 
I think that this works ok. It uses some arithmetic to determine the worksheet
name from the row number:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRng As Range
Dim iCtr As Long
Dim wks As Worksheet

If Target.Cells.Count > 1 Then Exit Sub

Set myRng = Me.Range("e11")
For iCtr = 3 To 36 Step 2
Set myRng = Union(myRng, Me.Cells(iCtr + 10, "E"))
Next iCtr

If Intersect(Target, myRng) Is Nothing Then Exit Sub

Set wks = Nothing
On Error Resume Next
Set wks = Me.Parent.Worksheets("Room " & (Target.Row - 9) \ 2)
On Error GoTo 0

If wks Is Nothing Then
MsgBox "not found"
Else
wks.Visible = CBool(Target.Value = 0)
End If

End Sub
 
Okay now whenever I put anything into those cells it says "Not Found" in a box. Did I do something wrong?
 
Back
Top