Hide Worksheet Based on Cell Value

[iken]

Hi All,

I have searched far and wide for answers to this, and there are some that are close, but none that suits my specific needs.
I have a workbook with over 100 sheets, each named, and not in order (ie sheet 10 could be the 30th sheet). On the first sheet called "Trade List", I have all teh sheets named in Column D (from D10 to D110). In column E (from E10 to E110) I have a "1" or "0" depending if I want teh corresponding worksheet visable (1 = viable, 0 = hidden).

What VBA code do I need to put into "Trade List" worksheet, so the respective sheets will hide/unhide based on values in column E?

I have attached a shrunk down version of the workbook which may help to understand what I have. Basically, if tehre is a 1 (shown as a tick) in column E, the shhet needs to be unhidden. Jote Summary, Clinet Suummary will always be visable.

Any help would be greatly appreciated.
Thanks

 

[SkipVought]

Hi,

You will need VBA (macro)

Sub HideUnhideSheets()
Dim r as Range

For Each r in Sheets("Trade List").Range("D12:D110")
   Select Case r.offset(0,1).Value
      Case 1
          Sheets(r.Value).Hidden = xlSheetVisible
      Case 0
          Sheets(r.Value).Hidden = xlSheetHidden
   End Select
Next
End Sub

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[iken]

Hi Skip,
I tried to copy the code into a few places (Module 1, into code for Trade List worksheet) and couldn't get this to work.

I did note I got teh column wrong, should have been C and not D so I changed this alomg with the offset - still didn't work as below.

Sub HideUnhideSheets()
Dim r As Range

For Each r In Sheets("Trade List").Range("C12:C110")
Select Case r.Offset(0, 2).Value
Case 1
Sheets(r.Value).Hidden = xlSheetVisible
Case 0
Sheets(r.Value).Hidden = xlSheetHidden
End Select
Next
End Sub


Any chance you could paste into correct location of workbook I uploaded and re-upload.

Thanks,

 

[SkipVought]

Sorry, I posted, from memory, the incorrect syntax for making sheets visible or not.

Your data begins in row 10, not row 12. The last row is 16, not 110. Furthermore, if you add additional sheets, if you hard-coded 16 as the last row, then TILT! Consequently the last row will be calculated at run time.

Sub HideUnhideSheets()
    Dim r As Range, rng As Range
    
    [b]'set the range for cells that contain sheet names[/b]
    Set rng = Sheets("Trade List").Cells(10, "C")
    Set rng = Range(rng, rng.End(xlDown))
    
    For Each r In rng
       Select Case r.Offset(0, 2).Value
          Case 1
              Sheets(r.Value).Visible = xlSheetVisible
          Case 0
              Sheets(r.Value).Visible = xlSheetHidden
       End Select
    Next
End Sub

Be sure that you save your workbook containing your macro as a Macro Enabled Workbook .xlsm.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[onlyadrafter]

Hello,

The code you have runs when you activate the macro. If you want the code to run when you enter a 0 or a 1 in column E then you can use the code below.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Selection, Range("E12:E110")) Is Nothing Then Exit Sub
        MY_SHEET = Range(Target.Address).Offset(0, -2).Value
        If Target.Value = 1 Then
            Sheets(MY_SHEET).Visible = True
        Else
            Sheets(MY_SHEET).Visible = False
        End If
End Sub

This code needs to go into the Trade List code window, not a standard module. The code can also be modified to select cells beyond row 110.


----------------------------------
Hope this helps.
----------------------------------

been away for quite a while
but am now back

 

[SkipVought]

There's actually a problem with using E10:E110, if a change is made in that range that has no data...

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim MY_SHEET As String, rng As Range
    
    'set the range for cells that contain sheet switch values
    Set rng = Sheets("Trade List").Cells(10, "E")
    Set rng = Range(rng, rng.End(xlDown))
    
    If Not Intersect(Target, rng) Is Nothing Then
        MY_SHEET = Target.Offset(0, -2).Value
        If Target.Value = 1 Then
            Sheets(MY_SHEET).Visible = xlSheetVisible
        Else
            Sheets(MY_SHEET).Visible = xlSheetHidden
        End If
    End If
End Sub

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[onlyadrafter]

Forgot to say that the code does not take into account any error checking. It will also fail if the sheet names do not exactly match the list in the Trade List spreadsheet.

In your code, doesn't the set rng code cause problems if there are gaps in Column E?

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim MY_SHEET As String, rng As Range
    Set rng = Range("E10:E" & Range("E" & Rows.Count).End(xlUp).Row)
    If Not Intersect(Target, rng) Is Nothing Then
        MY_SHEET = Target.Offset(0, -2).Value
        If Target.Value = 1 Then
            Sheets(MY_SHEET).Visible = True
        Else
            Sheets(MY_SHEET).Visible = False
        End If
    End If
End Sub

This code needs to go into the Trade List code window, not a standard module.

----------------------------------
Hope this helps.
----------------------------------

been away for quite a while
but am now back