Worksheet Change: Multiple target addresses

[hensleyj]

I am trying to make the worksheet change, to look at 2 different cells and if they become YES or NO, call a macro.

I am new to worksheet change events, so im not sure how to do this. I can get 1 change to work...i cant get 2 target addresses to work.

Can anyone help?

Its important that the worksheet change event only occurs when G19 or G127 change.

Private Sub Worksheet_Change(ByVal Target As Range)


    If Target.Count > 1 Then Exit Sub
    If Target.Address(0, 0) = "G19" Then
        If Target.Value = "Yes" Then
        Call NoClaimBonus
  
        ElseIf Target.Value = "No" Then
            Worksheets("TD Payment Calculator").Unprotect
            Call NoNoClaimBonus
   End If
   
 If Target.Count > 1 Then Exit Sub   
     If Target.Address(0, 0) = "G127" Then
        If Target.Value = "Yes" Then
        Call NoClaimBonus
       
  
        ElseIf Target.Value = "No" Then
            Worksheets("TD Payment Calculator").Unprotect
            Call NoNoClaimBonus
            
        End If
        Application.EnableEvents = True
    End If
End Sub

 

[JoeMo]

You don't indicate what happens if C32 is changed, but see if this sorts your problem for the bit you posted:

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
                                    
            End Select
    Next c
End If
End Sub

 

[vipin30sep]

Thanks Joe,

I tried and included C32 conditions as well. Its not working. Here is the code I'm working. The scenario is as below,

when I select C4 dropdown, all the macros in C4 case are called and works well.
Now when I select something from C23, C23 case works well, but other two C4 and C32 are not working.

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

 

[vipin30sep]

Also to give an idea what I have used as Macro is below. This is a case of selective hiding/ unhiding rows in different sheets.

Sub Twentytwenty()
Sheets("Data Inputs").Rows("5:124").Hidden = False
Sheets("Data Inputs").Rows("125:324").Hidden = True
Sheets("Metrics Table").Range("3:9,25:31,47:53,69:76,92:98,114:120,136:142,158:164,180:186,202:208,224:230,246:253,269:275,291:298,314:320,336:343,359:365,381:387,403:409,425:431,447:453,469:475,491:497").EntireRow.Hidden = False
Sheets("Metrics Table").Range("514:521,537:543,559:565,581:588,604:610,626:632,648:654,670:676,692:698,714:720,736:742,758:765,781:787,803:810,826:832,848:855,871:877,893:899,915:921,937:943,959:965,981:987,1003:1009").EntireRow.Hidden = False
Sheets("Metrics Table").Range("10:24,32:46,54:68,77:91,99:113,121:135,143:157,165:179,187:201,209:223,231:245,254:268,276:290,299:313,321:335,344:358,366:380,388:402,410:424,432:446,454:468,476:490,498:513").EntireRow.Hidden = True
Sheets("Metrics Table").Range("522:536,544:558,566:580,589:603,611:625,633:647,655:669,677:691,699:713,721:735,743:757,766:780,788:802,811:825,833:847,856:870,878:892,900:914,922:936,944:958,966:980,988:1002,1010:1024").EntireRow.Hidden = True
End Sub

 

[JoeMo]

Thanks Joe,

I tried and included C32 conditions as well. Its not working. Here is the code I'm working. The scenario is as below,

when I select C4 dropdown, all the macros in C4 case are called and works well.
Now when I select something from C23, C23 case works well, but other two C4 and C32 are not working.

The worksheet-change event responds to cells that do not contain formulas and are changed manually. If you only change one of the three cells then only that cell's case will be executed. If you want to run all the macros for the two cells that were not changed then you need to put all those code blocks under each case.

 

[vipin30sep]

The worksheet-change event responds to cells that do not contain formulas and are changed manually. If you only change one of the three cells then only that cell's case will be executed. If you want to run all the macros for the two cells that were not changed then you need to put all those code blocks under each case.

Thanks. The cells do not contain any formulas, they are dropdown that a user can select. I want when C4 operation runs, C23 and C32 code should not run. the excel sheet should remain in the same state. But that's not happening. Say when I selected a dropdown value in C4 and I go and do some change event on C23, I don't want to make any changes for C4 function.

Not sure if I'm able to explain it clearly. Here is what I'm trying to do overall.

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.

 

[JoeMo]

The problem you describe is not being caused by the change event macro. It's likely related to one or more of the things your other macros that the change event macro calls are doing. You need to look carefully at those macros. Maybe step through them while watching the other two cells to see where the change to them is happening.

 

[vipin30sep]

The problem you describe is not being caused by the change event macro. It's likely related to one or more of the things your other macros that the change event macro calls are doing. You need to look carefully at those macros. Maybe step through them while watching the other two cells to see where the change to them is happening.

Thanks Joe,

You are right. The Macro works fine and there was issue with one of my Module macro. Have corrected it and it works fine. Thanks for your help.

 

[JoeMo]

Thanks Joe,

You are right. The Macro works fine and there was issue with one of my Module macro. Have corrected it and it works fine. Thanks for your help.

You are welcome - thanks for the reply.