Hide Worksheets Based on a Cell Value on Another Sheet

[markf5998]

I have a workbook with a sheet named "Summary" where A6:A55 are numbered from 1-50. Each of those rows has a corresponding worksheet named "1" through "50". I'd like to have worksheets 1-50 hidden, unless there is something on the "Summary" tab in D6:D55.

For example, If Summary!D6 is blank, then "1" is hidden, but if any value is entered into that cell, it becomes Unhidden. If Summary!D7 is blank, then "2" is hidden, etc.

I also have several other worksheets in the workbook which prevent me from using the solutions to similar problems that I've found online.

Any help would be greatly appreciated!

 

[JoeMo]

Try this with the sheet containing the Range D6:D55 that controls things active:

Sub HideUnhideSheets()
Dim rng As Range, shNam As String
Set rng = Range("D6:D55")
For Each c In rng
    shNam = c.Offset(0, -3).Value
    If IsEmpty(c) Then
        ThisWorkbook.Sheets(shNam).Visible = False
    Else
        ThisWorkbook.Sheets(shNam).Visible = True
    End If
Next c
End Sub

Note this can be setup to run automatically whenever a change is made to any cell in the Range D6:D55 by adding a few lines and placing it in a sheet module.

 

[markf5998]

Thank you for your help!

This works great when I tell the macro to run, but as you mentioned it would be nice if it could automatically run every time something was entered in d6:d56.

Being fairly new to VB, could you tell me what needs to be added to the code? Also, you said to put it in a sheet module...does this mean to paste the code onto the sheets named 1-50 in the Visual Basic Project Screen? Right now I just opened the VB editor, clicked "insert module" and pasted the code, then used alt+f8 and selected the macro to make it run.

Thanks again! I really appreciate the help!

 

[JoeMo]

Follow this procedure: 1) Copy the code below. 2) Open your workbook, then activate the sheet with the relevant Range D6:D55 on it. 3) Right-click the sheet tab (the tab at the bottom of the sheet where the sheet name is). 4) Select "View Code". 5) The VBE window will open. 6) Paste to the white space in the VBE window. 6) Press Alt + F11 keys to close the VBE window. 7) Save the workbook. Now you should see worksheets become visible/invisible whenever changes are made to cells in the Range D6:D55 (50 cells total). Note that I have not included any error checking so if you have fewer than 50 worksheets named "1", "2", .... , "50" or you change the name of any of these sheets you will get an error.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, shNam As String

Set rng = Range("D6:D56")
If Not Application.Intersect(Target, rng) Is Nothing Then
    For Each c In rng
        shNam = c.Offset(0, -3).Value
        If IsEmpty(c) Then
            ThisWorkbook.Sheets(shNam).Visible = False
        Else
            ThisWorkbook.Sheets(shNam).Visible = True
        End If
    Next c
End If
End Sub

 

[markf5998]

Thanks again for your reply and for spending your time to help resolve my problem!

When I enter anything in a cell in the d6:d56 range, the macro runs and the corresponding sheet becomes unhidden (or unhidden if I remove the value). But it also gives an error that says "Run time Error '9': Subscript out of Range". When I Debug, the line that looks like it is causing the error is:

ThisWorkbook.Sheets(shNam).Visible = False

It it makes a difference, column D is a text field. But I get the same issue whether I enter text or numbers.

Any idea what the problem could be?

Thanks again! Really appreciate it!

 

[JoeMo]

I think the problem is that I read in one of your posts that the relevant range is D6:D56 and I used this in the revised code. In fact your 50 sheets correspond to D6:D55. I believe if you simply change the code range [the statement: Set rng = Range("D6:D56")] to D6:D55 you will no longer get an error message. To access the code, right-click the sheet tab and choose "View Code", edit, press alt + F11, and save the workbook

 

[markf5998]

Perfect, this is awesome...Thank you again for your time & help!!