Hide Tab Based Upon Cell Value

L

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
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Sort by date Sort by votes
LPalmer said:
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.
Click to expand...
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:
Upvote 0
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!
 
Upvote 0
What sheet is the code in?
 
Upvote 0
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?
 
Upvote 0
What is the name of Tab 2?
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0
Glad you got it working & thanks for the feedback
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question Question
VBA to detect hidden rows. then Re-hide
Replies
7
Views
208
Skyybot
Skyybot
B
  • Solved
Another VBA question to hide/unhide rows based on cell value in another sheet
2
Replies
12
Views
1K
kevin9999
K
M
  • Question Question
Hide Rows based on cell with drop down values
Replies
1
Views
1K
kevin9999
K
N
  • Question Question
Hiding/showing rows between two values
Replies
1
Views
479
Joe4
Joe4
D
  • Question Question
Multiple Monitors - tab selection
Replies
5
Views
294
Candyman8019
C

Forum statistics

Threads
1,224,817
Messages
6,181,148
Members
453,021
Latest member
Justyna P

Share this page

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
Back
Top