CODEWITHSUNDEEP
excelvba
James Reed
James Reed

Reputation: 3

Conditional hiding worksheet from multiple selections

I need a sheet in Excel to activate if any cells in a column are selected as "Yes", but my VBA code won't stick - simple enough to do for one cell, but the whole column is throwing me. The cells are a drop down list with solely the options "Yes" or "No"

Currently trying:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$H$11:$H$23" Then
            If ActiveWorkbook.Worksheets("Sheet1").Range("H11:H23").Value = "Yes" Then
            Sheets("Sheet2").Visible = True
        Else
            Sheets("Sheet2").Visible = False
        End If
    End If
End Sub

Any tips? Thanks

Upvotes: 0

Views: 38

Answers (2)

Error 1004
Error 1004

Reputation: 8220

i think you could try:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim cell As Range, rng As Range
    Dim Inrng As Boolean

    If Not Intersect(Target, Me.Range("H11:H23")) Is Nothing Then
        'Set a boolean variable to false
        Inrng = False
        'Set a range to loop
        Set rng = Me.Range("H11:H23")
        'Start looping the range
        For Each cell In rng
            'Convert the value of a cell to Upper case to avoid case sensitive issues
            If UCase(cell.Value) = "YES" Then
                'Turn the variable to true if value appears in the range
                Inrng = True
                'Exit the loop to avoid time consuming
                Exit For
            End If

        Next cell

        If Inrng = True Then
            Worksheets("Sheet2").Visible = True
        Else
            Worksheets("Sheet2").Visible = False
        End If

    End If

End Sub

Upvotes: 1

Pᴇʜ
Pᴇʜ

Reputation: 57673

An easier solution without looping would be to count the Yes using WorksheetFunction.CountIf method.

Use the following to show Sheet2 if at least one cell has the Yes.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim TestRange As Range
    Set TestRange = Me.Range("H11:H23")

    If Not Application.Intersect(Target, TestRange) Is Nothing Then  'if target is in test range

        If Application.WorksheetFunction.CountIf(TestRange, "Yes") > 0 Then
            Worksheets("Sheet2").Visible = True
        Else
            Worksheets("Sheet2").Visible = False
        End If
    End If
End Sub

If all cells in the test range need to be Yes then change it to

If Application.WorksheetFunction.CountIf(TestRange, "Yes") = TestRange.Cells.Count Then

Upvotes: 2

Related Questions