Hide/Unhide Worksheets Based on Cell Value RRS feed

  • Question

  • I have read many other threads related to this, but as my workbook has about 50 different worksheets I have had to modify the code to incorporate the complicated logic. And for some reason, this code is still not working for me. Please help!

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Intersect(Worksheets("Cover").Range("A7"), Target) Is Nothing Then
            Application.ScreenUpdating = False
            Worksheets("Safety Net FSU").Visible = False
            Worksheets("Special Needs FSU").Visible = False
            Worksheets("Holocaust Survivors- IL").Visible = False
            Worksheets("Mental Health - IL").Visible = False
            Worksheets("SC - Training").Visible = False
            Worksheets("SC - Direct Service").Visible = False
            Worksheets("SC - IL").Visible = False
            Worksheets("YA at Risk").Visible = False
            Worksheets("Engage").Visible = False
            Worksheets("Holocaust Survivors- NY").Visible = False
            Worksheets("PIC Older Adults").Visible = False
            Worksheets("PIC Data Chart").Visible = False
            Worksheets("Socialization.Volunteerism").Visible = False
            Worksheets("Alliance").Visible = False
            Worksheets("CHI").Visible = False
            Worksheets("RCCs").Visible = False
            Worksheets("Transition Support").Visible = False
            Worksheets("Seaver Center").Visible = False
            Worksheets("WJCS").Visible = False
            Worksheets("Inclusion").Visible = False
            Worksheets("PIC Synagogues").Visible = False
            Worksheets("PIC Day Schools").Visible = False
            Worksheets("PIC Bukharian").Visible = False
            Worksheets("Mental Health - NY").Visible = False
            Worksheets("Employment").Visible = False
            Worksheets("C2C&E2W").Visible = False
            Worksheets("Pathways").Visible = False
            Worksheets("C2CHubs & NYLAG").Visible = False
            Worksheets("Microenterprise").Visible = False
            Worksheets("CUNY").Visible = False
            Worksheets("Safety Net").Visible = False
            Worksheets("NYLAG").Visible = False
            Worksheets("Single Stop").Visible = False
            Worksheets("JCH&NYLAG").Visible = False
            Worksheets("Day Camp").Visible = False
            Worksheets("Day Care").Visible = False
            Worksheets("Volunteer Initiative").Visible = False
            Worksheets("Spirituality - NY").Visible = False
            Worksheets("LWP").Visible = False
            Worksheets("Other").Visible = False
            Select Case Range("A7").Value
                Case "FSU: Aging - Safety Net Supports"
                    Worksheets("Safety Net FSU").Visible = True
                Case "FSU: Special Needs Inclusion"
                    Worksheets("Special Needs FSU").Visible = True
                Case "Israel: Aging - Holocaust Survivors"
                    Worksheets("Holocaust Survivors- IL").Visible = True
                Case "Israel: Aging - Professional Development"
                    Worksheets("Professional Development").Visible = True
                Case "Israel: Mental Health And Resilience"
                    Worksheets("Mental Health - IL").Visible = True
                Case "Israel: Spiritual Care - Training Programs"
                    Worksheets("SC - Training").Visible = True
                Case "Israel: Spiritual Care - Direct Service/Field Placement Sites"
                    Worksheets("SC - Direct Service").Visible = True
                Case "Israel: Spiritual Care - Developing the Field"
                    Worksheets("SC - Israel").Visible = True
                Case "Israel: Employment - Young Adults at Risk"
                    Worksheets("YA at Risk").Visible = True
                Case "New York: Aging - UJA-Federation of New York's Engage Jewish Service Corps"
                    Worksheets("Engage").Visible = True
                Case "New York: Aging - Holocaust Survivors"
                    Worksheets("Holocaust Survivors- NY").Visible = True
                Case "New York: Aging - Partners in Caring Older Adults"
                    Worksheets("PIC Older Adults").Visible = True
                    Worksheets("PIC Data Chart").Visible = True
                Case "New York: Aging - Socialization/Voluntarism"
                    Worksheets("Socializaiton.Volunteerism").Visible = True
                Case "New York: Aging - Jewish Healing and Hospice Alliance"
                    Worksheets("Alliance").Visible = True
                Case "New York: Aging - Community Health Initiative"
                    Worksheets("CHI").Visible = True
                Case "New York: Aging - Regional Care Centers"
                    Worksheets("RCCs").Visible = True
                Case "New York: Autism - Transition Support: Adolescents/Young Adults"
                    Worksheets("Transition Support").Visible = True
                Case "New York: Autism - Mt. Sinai Seaver Center"
                    Worksheets("Seaver Center").Visible = True
                Case "New York: Autism - WJCS Autism Family Center"
                    Worksheets("WJCS").Visible = True
                Case "New York: Autism - Disabilities Inclusion"
                    Worksheets("Inclusion").Visible = True
                Case "New York: Mental Health - Partners in Caring Synagogues"
                    Worksheets("PIC Synagogues").Visible = True
                    Worksheets("PIC Data Chart").Visible = True
                Case "New York: Mental Health - Partners in Caring Day Schools"
                    Worksheets("PIC Day Schools").Visible = True
                Case "New York: Mental Health - Partners in Caring Bukharian"
                    Worksheets("PIC Bukharian").Visible = True
                Case "New York: Mental Health and Resilience"
                    Worksheets("Mental Health - NY").Visible = True
                Case "New York: Employment - Community-Based Employment Services"
                    Worksheets("Employment").Visible = True
                Case "New York: Employment - Employment Services for C2C and E2W"
                    Worksheets("C2C&E2W").Visible = True
                Case "New York: Employment - Economic Empowerment (Pathways Program)"
                    Worksheets("Pathways").Visible = True
                Case "New York: Employment - Connect to Care Hubs & NYLAG Services"
                    Worksheets("C2CHubs & NYLAG").Visible = True
                Case "New York: Employment - Microenterprise and Entrepreneurship"
                    Worksheets("Microenterprise").Visible = True
                Case "New York: Employment - CUNY Career Services"
                    Worksheets("CUNY").Visible = True
                Case "New York: Safety Net - Safety Net Initiative"
                    Worksheets("Safety Net").Visible = True
                    Worksheets("NYLAG").Visible = True
                Case "New York: Safety Net - Single Stop Initiative"
                    Worksheets("Single Stop").Visible = True
                    Worksheets("JCH&NYLAG").Visible = True
                Case "New York: Safety Net - Day Camp Scholarships"
                    Worksheets("Day Camp").Visible = True
                Case "New York: Safety Net - Day Care Scholarships"
                    Worksheets("Day Care").Visible = True
                Case "New York: Safety Net - Safety Net Volunteer Initiative"
                    Worksheets("Volunteer Initiative").Visible = True
                Case "New York: Spiritual Care"
                    Worksheets("Spirituality - NY").Visible = True
                Case "New York: Live with Purpose - Jewish Service Enterprise Initiative"
                    Worksheets("LWP").Visible = True
                Case "Other - My grant does not fall into any of the categories listed."
                    Worksheets("Other").Visible = True
            End Select
            Application.ScreenUpdating = True
        End If
    End Sub

    Monday, May 11, 2015 3:16 PM

All replies

  • There is nothing wrong with your code - but it needs to be in the code module of the worksheet named "Cover" and you need to make sure that the values that can be entered into A7 match exactly with the values you are looking for in the code. And you need to make sure that events are enabled, so try running this in a standard codemodule

    Sub ResetEvents()

        If Not Application.EnableEvents Then
            MsgBox "Events were off!"
            Application.EnableEvents = True
            MsgBox "Events are on, so that was not the issue."
        End If
    End Sub

    Monday, May 11, 2015 5:46 PM
  • Worked perfectly! Thank you for those suggestions! 
    Monday, May 11, 2015 5:54 PM
  • Yay, Nooby!
    Monday, May 11, 2015 6:11 PM