Excel Hide Sheets Based on A Cell Value

[AB_2019]

Hi,

I am trying to create a workbook which hides inactive sheets based on a cell value on the front sheet. i.e if the cell value us "yes" it is visible, if it is "no" it is hidden.

Private Sub Worksheet_Change(ByVal Target As Range)
If [A9] = "Yes" Then
Sheets("VAR 001").Visible = True
Else
Sheets("VAR 001").Visible = False
End If
End Sub

I need this to happen for multiple sheets. I have used this macro which works on a single sheet but I would like the other sheets named VAR 002 up to VAR 250 to operate the same way but with each sheet referencing a different cell for Yes/No.

VAR 002 need to reference cell A10, VAR 003 needs to reference cell A11 and so on up to VAR250 referencing call A258.

Can you please help.

Thank you!

[#2 · Accepted solution]

@AB_2019  If the sheet names are tied to the row numbers, you can use the code below. It will only run when a cell in the range A9 to A258 is changed. It also works if you copy and paste "Yes" or another value into more than one cell at a time. (Note that this is case sensitive, so "yes" will hide the sheet and only "Yes" will show it)

Let me know how you get on with this.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRow As Integer
Dim mySheetName As String
Dim cel As Range

' watch only cells A9 to A258
If Not Intersect(Target, Range("A9:A258")) Is Nothing Then

' if more than one cell has been changed, we need to cath them all
    For Each cel In Target

' get the row of the changed cell
' subtract 8 to arrive at the sheet number
        myRow = cel.Row - 8

' build the sheet name
        mySheetName = "VAR " & Format(myRow, "000")


        If cel = "Yes" Then
            ThisWorkbook.Worksheets(mySheetName).Visible = True
        Else
            ThisWorkbook.Worksheets(mySheetName).Visible = False
        End If
    Next cel

End If

[#3]

@AB_2019  Glad this worked for you. It would be great if you could mark my answer as the solution.

Hiding rows is a different question. I'll put that into a new question for you, so it gets more attention.