Hide or unhide sheet depending on cell value

2
votes

I am looking to write a code about hiding or un-hiding worksheets in Excel depending the value of a cell.

I have reached to this

Sub Hide_Un()
    If Range("b4").Value = "yes" Then
        sheets(2).Visible = True
    ElseIf Range("b4").Value = "no" Then
        sheets(2).Visible = False
    End If

    If Range("b5").Value = "yes" Then
        sheets(3).Visible = True
    ElseIf Range("b5").Value = "no" Then
        sheets(3).Visible = False
    End If

There are about 100 sheets and I can't do this procedure every time I add a new sheet.

I need a code to hide or unhide each worksheet depending my declaration to a cell.

Example B1="yes" (visible) or B1="no" (not visible)

2
excelvba
So you have one sheet that says which sheets should be hidden going down Column B? - urdearboy
Yes that is corect - vasilis
What is the name of this sheet? Does it have a header? I ask because we need to know if Row 1 corresponds to Sheet(1) or Sheet(2). What row is the first "yes" or "no" on? Is row 4 the first "yes" or "no"? - urdearboy
The first sheet is named "TOC". and i am running below the macro - vasilis
Sub ListSheets() Dim ws As Worksheet Dim x As Integer x = 3 sheets("TOC").Range("A:A").Clear For Each ws In Worksheets sheets("TOC").Cells(x, 1) = ws.Name x = x + 1 Next ws End Sub - vasilis

2 Answers

2
votes

Put this code into the ThisWorkbook code sheet.

Option Explicit    

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    Select Case LCase(Sh.Name)
        Case "toc"
            If Not Intersect(Target, Sh.Range("B:B")) Is Nothing Then
                Dim t As Range
                For Each t In Intersect(Target, Sh.Range("B:B"))
                    If t.Row > 3 Then
                        Worksheets(t.Row - 2).Visible = _
                            CBool(LCase(t.Value) = "yes")
                    End If
                Next t
            End If
        Case Else
            'do nothing
    End Select

End Sub
2
votes
  1. Option Compare Text makes this non case sensitive. This way YES = yes. Without this Option, they would not be equal
  2. You may need to account for the option of the value being neither yes or no. What if value is y or n or yes with a lagging space?
  3. Using the sheet index number (Sheet(n)) can be problematic if users have the option to add/move/delete sheets in the book.
  4. It looks like the row number relates to sheet number by Sheet # = Row -2. I am starting the loop at 4. The lowest this could be is 3 - other wise you will end up trying to hide a sheet that cannot exist
Option Explicit
Option Compare Text

Sub Hide_Un()

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("TOC")
Dim i

Application.ScreenUpdating = False
    For i = 4 To ws.Range("B" & ws.Rows.Count).End(xlUp).Row
        If ws.Range("B" & i) = "yes" Then
            ThisWorkbook.Sheets(i - 2).Visible = xlSheetVisible
        ElseIf ws.Range("B" & i) = "no" Then
            ThisWorkbook.Sheets(i - 2).Visible = xlSheetHidden
        Else 'What if the cell is neither?
            'Do what you want if the cell is not "yes" or "no"
        End If
    Next i
Application.ScreenUpdating = True

End Sub