Hide Sheets/tabs from the cell ? Possible ?

[audrey]

Hi,

I have an excel sheet that contains 120 tabs/sheets, but not all tabs are necesarily to be used.

I want to give the user flexibility to hide some of the sheets at the start sheet. Once he/she decides which sheets to be hidden with a simple Yes and No selection, I thought he/she can click the button and it hides all yes values. is that possible??? (and of course No values becomes unhidden)

 

[Andrew Poulsom]

Example for a list in A1:B17:

Private Sub CommandButton1_Click()
    Dim Cell As Range
    For Each Cell In Range("A1:A17")
        With Cell
            Worksheets(.Value).Visible = .Offset(, 1).Value <> "Yes"
        End With
    Next Cell
End Sub

Change the range reference to suit.

 

[VoG]

With a list of sheets starting in B4 and Yes/No in column C something like this should work

Sub HSheets()
Dim LR As Long, i As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = 4 To LR
    Sheets(Range("B" & i).Value).Hidden = Range("C" & i).Value = "Yes"
Next i
End Sub

 

[audrey]

I l give a try right away, and let you know the output. Thanks andrew.

 

[audrey]

Hi again, unless I am doing something really wrong, I couldnt make both work

Column B is the sheet name and Column E are the Hide = Yes Unhide = No options

 

[VoG]

Try

Sub HSheets()
Dim LR As Long, i As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = 6 To LR
    Sheets(Range("B" & i).Value).Hidden = Range("E" & i).Value = "Yes"
Next i
End Sub

 

[audrey]

Try

Sub HSheets()
Dim LR As Long, i As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = 6 To LR
    Sheets(Range("B" & i).Value).Hidden = Range("E" & i).Value = "Yes"
Next i
End Sub

Run time error 438
Object does not support this method or property

 

[VoG]

oops!

Sub HSheets()
Dim LR As Long, i As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = 6 To LR
    Sheets(Range("B" & i).Value).Visible = Range("E" & i).Value = "No"
Next i
End Sub

 

[audrey]

works like charm , thanks Vog