Hide or unhide sheets based on changing cell values

[bakarken]

Hi

I have a master sheet where cells E6 to E20 have either got the word 'HIDE' or 'UNHIDE' in them. Cells D6 to D20 have the names of each of the other 15 sheets in the workbook.

The 'HIDE' and 'UNHIDE' cells are displayed based on IF formulas i.e. =IF(E2=1,"HIDE","UNHIDE")

If possible, I'd like a VBA where each of the sheets will hide or unhide depending on the value on the master sheet

Thanks in advance!

 

[igold]

Does this do what you want:

Make sure that the name of the "Master" is that, or change the code to reflect what the name of your "Master" sheet is

Sub HideSheets()


    Dim i As Integer
    Dim wsMast As Worksheet: Set wsMast = Worksheets("Master")
    Dim nam As String
    
    With Worksheets("Master")
        For i = 6 To 20
            nam = wsMast.Range("D" & i)
            Worksheets(nam).Visible = wsMast.Range("E" & i) = "UNHIDE"
        Next
    End With
    
End Sub

 

[bakarken]

Hi Igold

Thanks for the reply, but this did not work...
Should the code go in the 'Master' sheet or the 'My workbook'?
I tried it in both but neither worked, just no responses.

 

[igold]

The code should go into a code module. I tested the code and it worked. Are you sure that your "Master" sheet is spelled exactly as it appears in the code with no leading or trailing spaces and correct Capitalization. The same holds true for your sheets, the sheet names must exactly match what you have listed on the Master in cells D6:D20.

 

[bakarken]

Hi sorry I've checked everything and it does not work for me. Im probably inputting the VBA incorrectly?
I'm pressing ALT+F11 > Pasting the above into 'This Workbook'

Please can you tell me exactly how to enter it if not the above?

 

[Fluff]

It needs to go in a regular module.
Alt F11 > Insert > Module > then paste the code in the new window.
To run the code (from the workbook) Alt F8 > select "HideSheets" > Run.

 

[igold]

Something else has to be going on. Even if the code was placed in the workbook module it should still run correctly...

Perhaps you could lay out your scenario again. Maybe one of us missed something in the post.

 

[Fluff]

Try this

Sub HideSheets()


    Dim i As Integer
    Dim wsMast As Worksheet: Set wsMast = Worksheets("Master")
    Dim nam As String
    
    With Worksheets("Master")
        For i = 6 To 20
            nam = wsMast.Range("D" & i)
            If Not Evaluate("isref('" & nam & "'!a1)") Then
               MsgBox nam & " Notfound"
            Else
               Worksheets(nam).Visible = wsMast.Range("E" & i) = "UNHIDE"
            End If
        Next
    End With
    
End Sub

Do you get the message box appearing at all?

 

[bakarken]

Fluff, that code has worked! No message box appears but thats fine I can live without.

Thank you both soooo much for your efforts!

 

[Fluff]

Glad it's working & thanks for the feedback.

Must admit I'm still confused as that's the same code as supplied by igold, with a couple of extra lines to check if the sheet exists.