Worksheet Change: Multiple target values not working

Question

Need to hide/ unhide rows in a multiple excel sheets based on dropdown cell values in a cell of another sheet. I have 3 sheets, Dashbaord, Data Inputs, Metrics table, I have put the following code in Dashboard sheet. The 3 cells mentioned below are dropdown and contain different set of values.

When a user selects one value say in dropdown C4, I'm hiding/ unhiding certain rows in all the 3 sheets mentioned above. But only the select case of cell C4 works fine. As you can see I have formulas for C23 and C32 as well, but it doesn't seem to work, not sure it is due to operations on same sheets

Another example, when say I select a dropdown value for Cell C23. The operations listed under C23 below works fine (respective macro is called and rows are hidden/ unhidden). But when I check the earlier selection on Cell value C4 is gone.

Please help

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
If Not Intersect(Target, Range("C4,C23,C32")) Is Nothing Then  

For Each c In Intersect(Target, Range("C4,C23,C32"))
    Select Case c.Address(0, 0)

        Case "C4"
            If Target.Value = "Ecommerce" Then Call Ecommerce
            If Target.Value = "Non-Commerce" Then Call NonCommerce
            If Target.Value = "Ecommerce & Non-Commerce" Then Call Both
            If Target.Value = "Select Ecommerce/Non-Commerce" Then Call Both

        Case "C23"
            If Target.Value = "Select Year" Then Call SelectYear
            If Target.Value = "2020" Then Call Twentytwenty
            If Target.Value = "2021" Then Call TwentyOne
            If Target.Value = "2022" Then Call TwentyTwo
            If Target.Value = "2023" Then Call TwentyThree
            If Target.Value = "2024" Then Call TwentyFour
            If Target.Value = "2025" Then Call TwentyFive

        Case "C32"
            If Target.Value = "Select PPC" Then Call SelectPPC
            If Target.Value = "PPC 2" Then Call PPCTwo
            If Target.Value = "PPC 3" Then Call PPCThree
            If Target.Value = "PPC 4" Then Call PPCFour
            If Target.Value = "PPC 5" Then Call PPCFive
            If Target.Value = "PPC 6" Then Call PPCSix
            If Target.Value = "PPC 7" Then Call PPCSeven

        End Select
Next c
End If
End Sub

I want the sheet to operate on all the 3 cells together, right now its broken and only one works at a time. Appreciate your help.

This is unrelated to your question, so I apologize, but seeing as you have a separate macros for every single year, are you sure that's necessary? Have you learned how to use parameters within a macro? That way, you could perahps almost remove the If statements altogether and just call Case "C23" // Call myYearMacro(Target.Value)? — BruceWayne
@BruceWayne - Yes, I will do that to optimize the code. Appreciate your response. — V Baj
A quick test - instead of Select Case for the three options, what if you just did If c.Address(0,0) = "C4" Then // [those options] // End If // If c.Address(0,0) = "C23" Then // [those options] // End If // If c.Address(0,0) = "C32" Then // [those options // End If. That way, it'll check c.Address() three times, instead of just the once...? — BruceWayne

Profex Profex · Accepted Answer · 2019-02-14T22:09:46

Reading the comments, it sounds like every time you select a different value from one of the three drop downs, you want to run three macros, depending on the values selected. If that is the case, then you don't need to iterate through the Target cells (only one can be assigned, using the drop down anyway).

All you need to do is identify that one of the three cells has been changed and then based on the three specific cells, run the corresponding macros.

Private Sub Worksheet_Change(ByVal Target As Range)
    ' This is all that you have to check, before deciding to run the macros
    If Intersect(Target, Range("C4,C23,C32")) Is Nothing Then Exit Sub

    Select Case Range("C4").Value
    Case "Ecommerce"
        Call Ecommerce
    Case "Non-Commerce"
        Call NonCommerce
    Case "Ecommerce & Non-Commerce", "Select Ecommerce/Non-Commerce"
        Both
    End Select

    Select Case Range("C23").Value
    Case "Select Year"
        Call SelectYear
    Case "2020"
        Call Twentytwenty
    Case "2021"
        Call TwentyOne
    Case "2022"
        Call TwentyTwo
    Case "2023"
        Call TwentyThree
    Case "2024"
        Call TwentyFour
    Case "2025"
        Call TwentyFive
    End Select

    Select Case Range("C32").Value
    Case "Select PPC"
        Call SelectPPC
    Case "PPC 2"
        Call PPCTwo
    Case "PPC 3"
        Call PPCThree
    Case "PPC 4"
        Call PPCFour
    Case "PPC 5"
        Call PPCFive
    Case "PPC 6"
        Call PPCSix
    Case "PPC 7"
        Call PPCSeven
    End Select
End Sub

Or better yet, listen to BruceWayne and you end up with something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    ' This is all that you have to check, before deciding to run the macros
    If Intersect(Target, Range("C4,C23,C32")) Is Nothing Then Exit Sub

    Select Case Range("C4").Value
    Case "Ecommerce"
        Ecommerce
    Case "Non-Commerce"
        NonCommerce
    Case "Ecommerce & Non-Commerce", "Select Ecommerce/Non-Commerce"
        Both
    End Select

    If IsNumeric(Range("C23").Value) Then SelectYear CInt(Range("C23").Value)

    If IsNumeric(Mid(Range("C32").Value, 5)) Then SelectPPC CInt(Mid(Range("C32").Value, 5))
End Sub

Sub SelectYear(Year As Integer)
    ' Do stuff
End Sub

Sub SelectPPC(Value As Integer)
    ' Do stuff
End Sub

FYI, You don't really need to use Call. The only real difference that it makes is whether parenthesis are required or not, when a Sub-routine has parameters.

Worksheet Change: Multiple target values not working

1 Answers