VBA to Hide/Unhide Sheets based on a table

yanqing06

New Member
Joined
Mar 3, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hi,

I have a master table with the following structure:

Row 1Column 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 2Legal feeLegalY
Row 3Admin fee for GHIGHI AdminY
Row 4Admin fee for XYZXYZ AdminN

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.

VBA Code:
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!
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Lian

New Member
Joined
Jan 26, 2021
Messages
33
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
VBA Code:
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

Well-known Member
Joined
May 1, 2002
Messages
1,785
Office Version
  1. 365
Platform
  1. Windows
Excel has a built-in "WorkSheet_Change" event....
Test on a COPY of your work, first!
Eg...
VBA Code:
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..
 
Last edited:

yanqing06

New Member
Joined
Mar 3, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Excel has a built-in "WorkSheet_Change" event....
Test on a COPY of your work, first!
Eg...
VBA Code:
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

Well-known Member
Joined
May 1, 2002
Messages
1,785
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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:

VBA Code:
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

Well-known Member
Joined
May 1, 2002
Messages
1,785
Office Version
  1. 365
Platform
  1. Windows
Sorry - it still wasn't absolutely right.

This should now work:
VBA Code:
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

New Member
Joined
Mar 3, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

VBA Code:
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

New Member
Joined
Jan 26, 2021
Messages
33
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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.

VBA Code:
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

New Member
Joined
Jan 26, 2021
Messages
33
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,998
Messages
5,622,097
Members
415,876
Latest member
csibonga2k17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top