Running Event Handler Change with Multiple cells

[PVA0724]

Hi Members

After a couple of research I was able to make the code below, where basically I need the macro to run either the change happen in cell A5 or G23, but when I run it I got "Ambigous Name Deteted" error. Also in the same worksheet I have another Worksheet Change event, so I don't know if I have to group all the changes in one Sub or if I have to open a Sube per each change.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim CriticalCells As Range
Set CriticalCells = Range("A5", "G23")
    
If Application.Intersect(Target, CriticalCells).Address = Target.Address Then
    If Target.Value <> "" Then
        Select Case Target.Value
            Case "Código de Compañía"
                Call CtaCtrl
            Case "Company Code"
                Call CtaCtrlEng
        End Select
    End If
End Sub

As working for me with Targets is sort of a new thing I'm not familiar what I'm doing wrong. Any assistance will be more than welcome. Thanks.

 

[Gavin T]

You need to combine your two procedures into one. I'd recommend storing them in a module and then calling them on condition from the worksheets' private module. Can you paste both of your procedures?

 

[PVA0724]

Hi Gavin

Thanks for the reply

Here are the two codes

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$C$9" Then
        Select Case Target.Value
            Case "Block", "Unblock", "Modification"
                Call JBACond2
            Case "Bloqueo", "Desbloqueo", "Modificación"
                Call JBACond1
        End Select
    End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim CriticalCells As Range
Set CriticalCells = Range("A5", "G23")
    
If Application.Intersect(Target, CriticalCells).Address = Target.Address Then
    If Target.Value <> "" Then
        Select Case Target.Value
            Case "Código de Compañía"
                Call CtaCtrl
            Case "Company Code"
                Call CtaCtrlEng
        End Select
    End If
End Sub

Both of them are in Sheet2...but I still need to add a couple more, so I appreciate if you could explain me how to add multiple events in one sheet as I need to replicate the process in other worksheets.

Thanks

 

[Gavin T]

You can't add multiple events in the same worksheet module. Instead, you can use an if statement to determine which code to run for example look at the following code.

Put this in the worksheets private module:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim CriticalCells As Range
    
    Set CriticalCells = Range("A5", "G23")
    
    If Target.Address = "$C$9" Then
        Call Procedure1(Target)
    ElseIf Application.Intersect(Target, CriticalCells).Address = Target.Address Then
        If Target.Value <> "" Then Call Procedure2(Target)
    End If
End Sub

And this in a public module (Insert>Module).

Sub Procedure1(Target As Range)
    
    If Target.Address = "$C$9" Then
        Select Case Target.Value
            Case "Block", "Unblock", "Modification"
                Call JBACond2
            Case "Bloqueo", "Desbloqueo", "Modificación"
                Call JBACond1
        End Select
    End If
End Sub

Sub Procedure2(Target As Excel.Range)

        Select Case Target.Value
            Case "Código de Compañía"
                Call CtaCtrl
            Case "Company Code"
                Call CtaCtrlEng
        End Select
    End If
End Sub

Be wary for the targets that overlap. If you have multiple if statements that have ranges that overlap then it will only run the first one that is true.

 

[PVA0724]

Hi Gavin

Thanks for the reply - sorry not come back to you yesterday...have an emergency to attend - I have copied the codes as you explain above, however now I'm getting an error "Argument Not Optional" in the following code, at line "Call Procedure1"

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim CriticalCells As Range
    
    Set CriticalCells = Range("A5", "G23")
    
    If Target.Address = "$C$9" Then
        [B][COLOR=#ff0000]Call Procedure1
[/COLOR][/B]    ElseIf Application.Intersect(Target, CriticalCells).Address = Target.Address Then
        If Target.Value <> "" Then Call Procedure2
    End If
End Sub

Again thanks for all your help

 

[Gavin T]

Your code is not identical to my code in Post #4. My code has these parameters when you call the procedures. Try adding them.
.

Call Procedure1(Target)
Call Procedure2(Target)

 

[PVA0724]

Hi Gavin

Thanks again I have change the code as you point - I thought the target in () referred to Procedure code - however now I'm having object variable or with object variable not set. The code in private module is and the error show up in the elseif line

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim CriticalCells As Range
    Set CriticalCells = Range("A5", "C5")
    If Target.Address = "$C$9" Then
        Call Procedure1(Target)
    ElseIf Application.Intersect(Target, CriticalCells).Address = Target.Address Then
        If Target.Value <> "" Then Call Procedure2(Target)
    End If
End Sub

The procedure that is being called in the public module is

Sub Procedure1(Target As Range)
    
    If Target.Address = "$C$9" Then
        Select Case Target.Value
            Case "Block", "Unblock", "Modification"
                Call JBACond2
            Case "Bloqueo", "Desbloqueo", "Modificación"
                Call JBACond1
        End Select
    End If
End Sub
Sub Procedure2(Target As Range)
    
        Select Case Target.Value
            Case "Código de Compañía"
                Call JBATaxCod
            Case "Company Code"
                Call JBATaxCoding
        End Select
End Sub

Sorry to bother...but this is my first time I have to deal with events changes based on multiple cells...and what I know is thanks to forums like this.

 

[mikerickson]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
    Select Case Target.Address
        Case "$C$9"
            Select Case Target.Value
                Case "Block", "Unblock", "Modification"
                    Call JBACond2
                Case "Bloqueo", "Desbloqueo", "Modificación"
                    Call JBACond1
            End Select
        Case "$A$5", "$G$23"
            If Target.Value <> "" Then
                Select Case Target.Value
                    Case "Código de Compañía"
                        Call CtaCtrl
                    Case "Company Code"
                        Call CtaCtrlEng
                End Select
            End If
    End Select
End Sub

 

[PVA0724]

It worked, Thank you so much Gavin and Mike for your help and assistance.