Search code examples
excelvbaloopscellshow-hide

Trying to hide sheets based on cell value for a range of cells to a range of sheets

I have a workbook that is getting very packed when it comes to the worksheets. I do not use all of them all the time, and so I want to hide unused ones at the time based on what i'm working on.

I have a main Index that has a column that has G2:G30 for the range of values to indicate if the sheet will be shown. I have a range of sheets that have been numbered for referencing below that range from 1,2,3,..... to 26. I have a Simple VBA solution that will show the single G2 of "Y" to show or to hide otherwise. I have zero clue about how this will look to iterate through these 2 ranges to match things up. They are incremental.

Private Sub Worksheet_Change(ByVal Target As Range)
    If [G2] = "Y" Then
        Sheets("1").Visible = True
    Else
        Sheets("1").Visible = False
    End If
End Sub

I really need this to iterate through the range to indicate to the sheets in the worksheet.

Solution

  • I'd like to suggest a little change to your request. If you add the sheet name in cells H2:H30, then you can do something like this:

    Dim SheetRef As Range
Dim TargetSheet As Worksheet
For Each SheetRef In Range("G2:G30")
    Set TargetSheet = ThisWorkbook.Worksheets(SheetRef.Offset(0, 1).Value)
    TargetSheet.Visible = (UCase(SheetRef.Value) = "Y")
Next

    This will read the name of the sheet from the cell to the right of G2, G3 etc and use that to control the visibility.
    If you don't do that, you will have to find another way of knowing what toggle refers to what sheet.