How do I HIDE or SHOW sheets based on a cell value?

R

rdep39

New Member
Joined
Aug 18, 2014
Messages
6
I have a workbook with 19 sheets. I would like to be able to create a way to UNHIDE a named sheet if a single Cell Value (on sheet 1) = YES. Otherwise, I would like the sheet to stay hidden. Is there a way to accomplish this and if so, how?
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Sort by date Sort by votes
This can be done using a worksheet change event macro in Sheet1. You could probably get some help with this if you post the cell address for the cell on sheet1 you want to base things on and the name of the sheet to be unhidden.
 
Upvote 0
Code: 
Sub unHideSheets()

    Dim sheetName(19) As String
    
    For x = 1 To 19 Step 1
        sheetName(x) = Sheets(1).Cells(x, 1)
            If Cells(x, 2).Value = "YES" Then
                Sheets(sheetName(x)).Visible = False
            Else
                Sheets(sheetName(x)).Visible = True
            End If
    Next x
End Sub

I don't think this is want you want, but it's what I have assumed based on your detail. This will take the first sheet in your workbook and hide things based on a table such as below.
AB
1Name1YES
2Name2YES
3Name3
......
18Name18
19Name19YES

<tbody>
</tbody>
 
Upvote 0
NeonRedSharpie said:
Code: 
Sub unHideSheets()

    Dim sheetName(19) As String
    
    For x = 1 To 19 Step 1
        sheetName(x) = Sheets(1).Cells(x, 1)
            If Cells(x, 2).Value = "YES" Then
                Sheets(sheetName(x)).Visible = False
            Else
                Sheets(sheetName(x)).Visible = True
            End If
    Next x
End Sub

I don't think this is want you want, but it's what I have assumed based on your detail. This will take the first sheet in your workbook and hide things based on a table such as below.
AB
1Name1YES
2Name2YES
3Name3
......
18Name18
19Name19YES

<tbody>
</tbody>
Click to expand...
There must be at least one visible sheet in a workbook - you need an error trap in case all sheets are listed as YES.
 
Upvote 0
Again, I'm just assuming his data so I made assumptions. I guess he had 19 ADDITIONAL worksheets, not including the index sheet. Really we're just grasping at straws until clarification.
 
Upvote 0
Yes, sheet 1 named: Job Setup will always be visible. I'd like it to run like this: IF 'Job Setup'!C16="NO" then HIDE Sheets "Warehouse" and "Warehouse Calcs"

There would be a grand total of 8 cell values from C16 to C23 that would each have a different sheet(s) to HIDE if =NO.

I'm inexperienced with using macros and VBA in excel, but it seems like this should be possible.
 
Upvote 0
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim hideVal As Integer
    Dim showVal As Integer


    With Sheets("Job Setup")
    
    
    For x = 16 To 23 Step 1
        If UCase(.Cells(16, 3)) = "NO" Then
            hideVal = x
            GoTo Hide
        Else
            showVal = x
            GoTo Show
        End If
more:
    Next x
    
End With


Exit Sub
    
Hide:


Select Case hideVal


    Case 16
        Sheets("SheetNameHere").Visible = False
        Sheets("SheetNameHere").Visible = False
    Case 17
        Sheets("SheetNameHere").Visible = False
        Sheets("SheetNameHere").Visible = False
    Case 18
        Sheets("SheetNameHere").Visible = False
        Sheets("SheetNameHere").Visible = False
    Case 19
        Sheets("SheetNameHere").Visible = False
        Sheets("SheetNameHere").Visible = False
    Case 20
        Sheets("SheetNameHere").Visible = False
        Sheets("SheetNameHere").Visible = False
    Case 21
        Sheets("SheetNameHere").Visible = False
        Sheets("SheetNameHere").Visible = False
    Case 22
        Sheets("SheetNameHere").Visible = False
        Sheets("SheetNameHere").Visible = False
    Case 23
        Sheets("SheetNameHere").Visible = False
        Sheets("SheetNameHere").Visible = False
    Case Else


End Select
GoTo more


Show:
Select Case showVal


    Case 16
        Sheets("SheetNameHere").Visible = True
        Sheets("SheetNameHere").Visible = True
    Case 17
        Sheets("SheetNameHere").Visible = True
        Sheets("SheetNameHere").Visible = True
    Case 18
        Sheets("SheetNameHere").Visible = True
        Sheets("SheetNameHere").Visible = True
    Case 19
        Sheets("SheetNameHere").Visible = True
        Sheets("SheetNameHere").Visible = True
    Case 20
        Sheets("SheetNameHere").Visible = True
        Sheets("SheetNameHere").Visible = True
    Case 21
        Sheets("SheetNameHere").Visible = True
        Sheets("SheetNameHere").Visible = True
    Case 22
        Sheets("SheetNameHere").Visible = True
        Sheets("SheetNameHere").Visible = True
    Case 23
        Sheets("SheetNameHere").Visible = True
        Sheets("SheetNameHere").Visible = True
    Case Else


End Select
GoTo more




End Sub


Right click on your sheet and hit "VIEW CODE". Then paste this in. Change your "SheetNameHere" to your actual sheets you want to hide. The Case16 et.al is the row value.
 
Upvote 0
It appears that the code is running only based on the YES or NO answer of cell C16 on Job Setup. YES unhides all sheets and NO hides all sheets (except Job Setup).

Sub HideShow()


End Sub
Private Sub Worksheet_Change(ByVal Target As Range)


Dim hideVal As Integer
Dim showVal As Integer




With Sheets("Job Setup")


For x = 16 To 23 Step 1
If UCase(.Cells(16, 3)) = "NO" Then
hideVal = x
GoTo Hide
Else
showVal = x
GoTo Show
End If
more:
Next x

End With




Exit Sub

Hide:




Select Case hideVal




Case 16
Sheets("Warehouse").Visible = False
Sheets("Warehouse Calcs").Visible = False
Case 17
Sheets("EPS").Visible = False
Sheets("EPS Calcs").Visible = False
Case 18
Sheets("Cement").Visible = False
Sheets("Cement Calcs").Visible = False
Case 19
Sheets("Key Deck Mesh").Visible = False
Sheets("KD Mesh Calcs").Visible = False
Case 20
Sheets("Acoustical").Visible = False
Sheets("Acoustical Calcs").Visible = False
Case 21
Sheets("Steel Deck").Visible = False
Sheets("Steel Deck Calcs").Visible = False
Case 22
Sheets("Tectum").Visible = False
Sheets("Tectum Calcs").Visible = False
Case 23
Sheets("Loadmaster").Visible = False
Sheets("LM Calcs").Visible = False
Case Else




End Select
GoTo more




Show:
Select Case showVal




Case 16
Sheets("Warehouse").Visible = True
Sheets("Warehouse Calcs").Visible = True
Case 17
Sheets("EPS").Visible = True
Sheets("EPS Calcs").Visible = True
Case 18
Sheets("Cement").Visible = True
Sheets("Cement Calcs").Visible = True
Case 19
Sheets("Key Deck Mesh").Visible = True
Sheets("KC Mesh Calcs").Visible = True
Case 20
Sheets("Acoustical").Visible = True
Sheets("Acoustical Calcs").Visible = True
Case 21
Sheets("Steel Deck").Visible = True
Sheets("Steel Deck Calcs").Visible = True
Case 22
Sheets("Tectum").Visible = True
Sheets("Tectum Calcs").Visible = True
Case 23
Sheets("Loadmaster").Visible = True
Sheets("LM Calcs").Visible = True
Case Else




End Select
GoTo more








End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question
Hiding rows based on cell value
Replies
2
Views
100
smozgur
smozgur
M
  • Question
Need multiple buttons to hide/show rows on another tab
Replies
8
Views
391
skillilea
S
B
  • Solved
Worksheet Change - multiple Loops in one sub
2
Replies
15
Views
252
mumps
mumps
B
  • Solved
Another VBA question to hide/unhide rows based on cell value in another sheet
2
Replies
12
Views
431
kevin9999
K
D
  • Solved
Hiding Single and Multiple Rows Based on Cell Selection(s)
Replies
1
Views
736
ddocg
D

Forum statistics

Threads
1,214,970
Messages
6,122,514
Members
449,088
Latest member
RandomExceller01

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