VBA to Hide/Unhide Sheets based on a table

[yanqing06]

Hi,

I have a master table with the following structure:

Row 1	Column A (Item)	Column B (Corresponding Sheet Name)	Column C (To unhide corresponding sheet (based on IF statement results of other values in the table))
Row 2	Legal fee	Legal	Y
Row 3	Admin fee for GHI	GHI Admin	Y
Row 4	Admin fee for XYZ	XYZ Admin	N

I want to Unhide Sheet "Legal" to reveal details about Legal Fee - only when the relevant criteria is met and Column C reflects "Y". I need the sheets where Column C is "N" to be hidden, i.e. Sheet "XYZ Admin".
I am using the following code assigned to a click button. As the Y/N in the table constantly changes, i will need each click to hide/unhide relevant sheets based on recent data.

Sub Hide_Sheet()

If [C2] = "Y" Then
Sheets("Legal").Visible = True
Else
Sheets("Legal").Visible = False
End If

If [C3] = "Y" Then
Sheets("GHI Admin").Visible = True
Else
Sheets("GHI Admin").Visible = False
End If

End Sub

However, I have more than 200 rows with items with unique work sheet name. Instead of replicating and manually editing the formula for 200 times, is there a way that i could make my formula dynamic by referencing to the cell values as sheet name?

Please note that i have other sheets with key data to be shown in the same workbook but not subject to the above criteria.

I am very new to VBA and only start working on it for a few days. Thanks for all your help!

 

[Lian]

Sub hideSheet()

Dim iLastCell As Long
Dim shtName As String
‘ get the last cell of column B, which you have sheet name
iLastCell = Cells(Rows.Count, "B").End(xlUp).Row

For i = 2 To iLastCell
    shtName = Cells(i, "B")
' Ucase function to avoid lowercase or upper case   
    If UCase(Cells(i, "C")) = "Y" Then
        Sheets(shtName).Visible = True
    Else
        Sheets(shtName).Visible = False
    End If

Next

End Sub

 

[sykes]

Excel has a built-in "WorkSheet_Change" event....
Test on a COPY of your work, first!
Eg...

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
           If .Column = 3 And .Row > 2 And .Row < 1000 And .Value = "Y" Then
            Sheets("Legal").Visible = True
           Else
            Sheets("Legal").Visible = False
           End If
    End With
End Sub

...Put your own code in, obvs..

 

[yanqing06]

Excel has a built-in "WorkSheet_Change" event....
Test on a COPY of your work, first!
Eg...

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
           If .Column = 3 And .Row > 2 And .Row < 1000 And .Value = "Y" Then
            Sheets("Legal").Visible = True
           Else
            Sheets("Legal").Visible = False
           End If
    End With
End Sub

...Put your own code in, obvs..

Thanks so much!

I have tried the code but unfortunately it does not work for me.

I also need the corresponding sheets i.e. Sheets ("GHI Admin") to be visible only when corresponding row C3 is Y; and Sheets ("XYZ Admin") to be visible only when row C4 is Y, etc. Based on the table in the original post, i need both sheet Legal and GHI Admin to be unhidden upon trigger.

The current code only seem to unhide ("Legal") when criteria is met?

Is there any workaround for this?

 

[sykes]

Sorry - didn't fully read your requirements.
This should work, once again, it needs to be placed into the worksheet_change event of the sheet in question:

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
           If .Column = 3 And .Row > 2 And .Row < 1000 And .Value = "Y" Then
            Sheets(.Offset(0, -1).Value).Visible = True
           Else
           Sheets(.Offset(0, -1).Value).Visible = False
           End If
    End With
End Sub

 

[sykes]

Sorry - it still wasn't absolutely right.

This should now work:

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
           If .Column <> 3 Or .Row < 2 Or .Row > 1000 Then Exit Sub
           
           If .Value = "Y" Then
            Sheets(.Offset(0, -1).Value).Visible = True
           Else
           Sheets(.Offset(0, -1).Value).Visible = False
           End If
    End With
End Sub

 

[yanqing06]

Sub hideSheet()

Dim iLastCell As Long
Dim shtName As String
‘ get the last cell of column B, which you have sheet name
iLastCell = Cells(Rows.Count, "B").End(xlUp).Row

For i = 2 To iLastCell
    shtName = Cells(i, "B")
' Ucase function to avoid lowercase or upper case  
    If UCase(Cells(i, "C")) = "Y" Then
        Sheets(shtName).Visible = True
    Else
        Sheets(shtName).Visible = False
    End If

Next

End Sub

Thank you Lian! This works like a charm.

Thanks so much skyes too.

I am also thinking based on Lian's code, is it possible to modify it to become - (i) upon Column C is selected as Y (ii) the workbook will create a new sheet for that cell, based on a prefixed name (based on column B) and a sheet template (say Sheet "Legal")?

Similarly, this needs to happen for my over 200 rows of data too.

 

[Lian]

Thank you Lian! This works like a charm.

Thanks so much skyes too.

I am also thinking based on Lian's code, is it possible to modify it to become - (i) upon Column C is selected as Y (ii) the workbook will create a new sheet for that cell, based on a prefixed name (based on column B) and a sheet template (say Sheet "Legal")?

Similarly, this needs to happen for my over 200 rows of data too.

Just for your reference.

Sub createSheet()

Dim iLastCell As Long
Dim shtName As String
Dim sht As Worksheet

iLastCell = Cells(Rows.Count, "B").End(xlUp).Row

For i = 2 To iLastCell
    shtName = Cells(i, "B")

    If UCase(Cells(i, "C")) = "Y" Then
        For Each sht In Sheets
        
        On Error Resume Next
            If Sheets(shtName) Is Nothing Then
                With Sheets.Add(after:=Sheets(Sheets.Count))
                    .Name = shtName
                End With
            End If
        Next
    End If
Next

End Sub

 

[Lian]

by the way, this is not the best solution, just for your reference.

You can try loop sheet firstly and then chk the mark if “Y”, maybe it will be more efficient.