Hide Excel sheet based on value of a cell in a range

0
votes

I'm trying to hide a sheet in an Excel workbook based on the contents of any of the cells in a given range.

Let's say I have two sheets - "Sheet1" and "Sheet2".
On Sheet1, I want to set up a range - cell C10 to F10.
Each of these cells can either be blank, or contain "Yes" or "No" - chosen from a dropdown box.
If ANY of the cells in the range are set to "Yes", I want Sheet2 to be visible, otherwise (if all the cells are either blank or contain "No") I want Sheet2 hidden.

I've tried various pieces of code, including the below.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rCell As Range

    Application.ScreenUpdating = False
    For Each rCell In Range("C10:F10")
        If rCell.Value = "Yes" Then
            Worksheets("Sheet2").Visible = True
        Else
            Worksheets("Sheet2").Visible = False
        End If
    Next rCell
    Application.ScreenUpdating = True
End Sub

I've got about as far as Sheet 2 being visible if all the cells equal "Yes" or if F10 equals "Yes", but not if only one of the cells contains "Yes".

2
excelvba
you could probably save some time and use a application.countif(wsrng,"Yes")>0 for your desired range, so you don't have to loop. if true, show, if false, hide. – Cyril
Worksheets("Sheet2").Visible = Application.Countif(Range("C10:F10"))>0 – Scott Craner
@ScottCraner - you forgot the criteria part of the COUNTIF. – Darren Bartrup-Cook
@DarrenBartrup-Cook which is why I answered, noticed that after the time limit was up. – Scott Craner

2 Answers

2
votes

No loop needed, and create an If to test whether the Cell that change is in the range to test, just to save some comp time:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("C10:F10"),Target) Is Nothing Then
        Worksheets("Sheet2").Visible = Application.Countif(Range("C10:F10"),"Yes")>0
    End If
End Sub
0
votes

Modify and try:

Option Explicit

Sub test()

    Dim ws As Worksheet
    Dim rng As Range, cell As Range
    Dim Hide As Boolean

    For Each ws In ThisWorkbook.Worksheets

        Set rng = ws.Range("C10:F10")

        For Each cell In rng
            Hide = False
            If cell.Value = "Yes" Then
                Hide = False
                Exit For
            Else
                Hide = True
            End If
        Next

        If Hide = True Then
            ws.Visible = False
        End If

    Next

End Sub