Hide Tab Based Upon Cell Value

LPalmer

New Member
Joined
Oct 8, 2019
Messages
12
Hello All! I was attempting to have a tab hidden based upon 'yes' or 'no' value placed into the cell of another tab. I have included my macro below (see very end for my issue) which is not working. :( Any recommendations appreciated! Thanks!

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   
   'Participant Update to Cell C8
   'The formula directly below allows us to change between the different cells that the participants will be updating as we move through the macro
   If Target.Address(0, 0) = "C8" Then
      'Message Box Appears and Unhides Rows if Value is less than 25
      If Target < 25 And Target <> "" Then
         Rows("10:11").EntireRow.Hidden = False
         MsgBox "Unless the plan has less than 250 participants, it would be unusual to have a sample size of less than 25. Verify that this is the sample size that was determined to be appropriate."
         
      'Hides Rows if Value is equal to or greater than 25
      ElseIf Target >= 25 Or Target = "" Then
         Rows("10:11").EntireRow.Hidden = True
        End If
        'Iteration of End Function Req'd for all embedded If-Then Statements in the macro'
         
    'Participant Update to Cell C12
    ElseIf Target.Address(0, 0) = "C12" Then
      'Hides Rows if Cell C12 equals "No"
      If Target = "YES" Then
         Rows("13:15").EntireRow.Hidden = False
      ElseIf Target = "NO" Then
         Rows("13:15").EntireRow.Hidden = True
         End If
         
'Why Don't you give Task 1C and Task 1D a shot!
         
     'Participant Update to Cell C24
     ElseIf Target.Address(0, 0) = "C24" Then
      'Unhides rows and unhides column in Tab 2 if C24 says "Yes"
      If Target = "YES" Then
         Rows("25").EntireRow.Hidden = False
         Rows("26").EntireRow.Hidden = False
         'Note that format below to reference cells/rows/columns in other worksheets
         'I found it best to copy and paste the tab name directly from the tab because sometimes just typing it doesn't work.
         With Worksheets("2) Participant Eligibility")
            .Columns("F:F").EntireColumn.Hidden = False
         End With
         'End With Function required when we are manipulating items on different excel worksheets
       End If
         
      If Target = "NO" Then
         Rows("25").EntireRow.Hidden = True
         Rows("26").EntireRow.Hidden = True
         With Worksheets("2) Participant Eligibility")
            .Columns("F:F").EntireColumn.Hidden = True
         End With
       End If
       
   'Participant Update to Cell C67
   'Shows Tab 6 if Cell C67 is "Yes" Otherwise Tab remains hidden
    Select Case Worksheets("1) Scoping").Range("C67").Value
    Case "YES"
    Worksheets("6) Safe Harbor & Profit Sharing").Visible = True
    Case " "
    Worksheets("6) Safe Harbor & Profit Sharing").Visible = False
    Case "NO"
    Worksheets("6) Safe Harbor & Profit Sharing").Visible = False
    End Select
      
  End If
End Sub
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
43,719
Office Version
365
Platform
Windows
I was attempting to have a tab hidden based upon 'yes' or 'no' value placed into the cell of another tab. I have included my macro below (see very end for my issue) which is not working.
Always best to describe in what way it "isn't working"
- Error message (what message on what line)
- Excel crashes
- Does nothing
- Writes "x" instead of "y"
- Works for fist sheet but not second sheet
etc

One possible reason is that the text in the cell you are checking does not exactly match the 'case' of the text in the code. see if this helps. If not, refer to my points above
Rich (BB code):
Select Case UCase(Worksheets("1) Scoping").Range("C67").Value)
 
Last edited:

LPalmer

New Member
Joined
Oct 8, 2019
Messages
12
Thank you for the feedback Peter. Nothing happens as I toggle between Yes, No or <blank> in cell C67. Unfortunately, the case of the text doesn't or your code edit suggestion didn't have an impact. Nothing happened. Any additional help greatly appreciated!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
36,145
Office Version
365
Platform
Windows
What sheet is the code in?
 

LPalmer

New Member
Joined
Oct 8, 2019
Messages
12
The code is in the sheet is in Tab 2. We were hoping to hide Tab 6 so not sure if there is code req'd in Tab 6 instead?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
36,145
Office Version
365
Platform
Windows
What is the name of Tab 2?
 

LPalmer

New Member
Joined
Oct 8, 2019
Messages
12
Apologies, I misspoke. The code is in Tab 1 labeled: "1) Scoping". Thanks
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
36,145
Office Version
365
Platform
Windows
In that case try
Rich (BB code):
Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   
   'Participant Update to Cell C8
   'The formula directly below allows us to change between the different cells that the participants will be updating as we move through the macro
   If Target.Address(0, 0) = "C8" Then
      'Message Box Appears and Unhides Rows if Value is less than 25
      If Target < 25 And Target <> "" Then
         Rows("10:11").EntireRow.Hidden = False
         MsgBox "Unless the plan has less than 250 participants, it would be unusual to have a sample size of less than 25. Verify that this is the sample size that was determined to be appropriate."
      
      'Hides Rows if Value is equal to or greater than 25
      ElseIf Target >= 25 Or Target = "" Then
         Rows("10:11").EntireRow.Hidden = True
      End If
      'Iteration of End Function Req'd for all embedded If-Then Statements in the macro'
      
      'Participant Update to Cell C12
   ElseIf Target.Address(0, 0) = "C12" Then
      'Hides Rows if Cell C12 equals "No"
      If Target = "YES" Then
         Rows("13:15").EntireRow.Hidden = False
      ElseIf Target = "NO" Then
         Rows("13:15").EntireRow.Hidden = True
      End If
      
      'Why Don't you give Task 1C and Task 1D a shot!
      
      'Participant Update to Cell C24
   ElseIf Target.Address(0, 0) = "C24" Then
      'Unhides rows and unhides column in Tab 2 if C24 says "Yes"
      If Target = "YES" Then
         Rows("25").EntireRow.Hidden = False
         Rows("26").EntireRow.Hidden = False
         'Note that format below to reference cells/rows/columns in other worksheets
         'I found it best to copy and paste the tab name directly from the tab because sometimes just typing it doesn't work.
         With Worksheets("2) Participant Eligibility")
            .Columns("F:F").EntireColumn.Hidden = False
         End With
         'End With Function required when we are manipulating items on different excel worksheets
      ElseIf Target = "NO" Then
         Rows("25").EntireRow.Hidden = True
         Rows("26").EntireRow.Hidden = True
         With Worksheets("2) Participant Eligibility")
            .Columns("F:F").EntireColumn.Hidden = True
         End With
      End If
   ElseIf Target.Address(0, 0) = "C67" Then
      'Participant Update to Cell C67
      'Shows Tab 6 if Cell C67 is "Yes" Otherwise Tab remains hidden
      Select Case UCase(Target)
         Case "YES"
            Worksheets("6) Safe Harbor & Profit Sharing").Visible = True
         Case " ", "NO"
            Worksheets("6) Safe Harbor & Profit Sharing").Visible = False
      End Select
   End If
End Sub
 

LPalmer

New Member
Joined
Oct 8, 2019
Messages
12
Unfortunately no luck as in no error messages and no response from macro. I tried another iteration on the last part and had success:

Code:
'Participant Update to Cell C67
   'Shows Tab 6 if Cell C67 is "Yes" Otherwise Tab remains hidden
     ElseIf Target.Address(0, 0) = "C67" Then
       If Target = "YES" Then
         Worksheets("6) Safe Harbor & Profit Sharing").Visible = True
         End If
       If Target = "NO" Then
         Worksheets("6) Safe Harbor & Profit Sharing").Visible = False
         End If
Thank you for your guidance!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
36,145
Office Version
365
Platform
Windows
Glad you got it working & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,090,459
Messages
5,414,643
Members
403,540
Latest member
mmorejon1215

This Week's Hot Topics

Top