How to call two Macros with the same cell change

[PVA0724]

Hi Members

I've been trying to figure out a way to call two macros based on a condition given by the same cell. So for example I have

Cell C9 = Bloqueo (for Spanish)

and through Macros I'm changing that to English so it will be like

Cell C9 = Block

so when C9 is Bloqueo I need the following code

Private Sub Worksheet_Change(ByVal Target As Range)

 If Target.Address = "$C$9" Then Call JBACond1
End Sub

But when C9 is Block I need the following code

Private Sub Worksheet_Change(ByVal Target As Range)

 If Target.Address = "$C$9" Then Call JBACond2
End Sub

So it's possible to trigger different macros based on how the statement of the same cell change.

Thanks in advance for your assistance.

 

[Smitty]

See if this does it for you:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#007F00">'   Code goes in the Worksheet specific module</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br>        <SPAN style="color:#007F00">'   Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> rng = Target.Parent.Range("C9")<br>             <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>            <SPAN style="color:#007F00">'   Only look at that range</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>            <SPAN style="color:#007F00">'   Action if Condition(s) are met (do your thing here...)</SPAN><br>            <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> Target.Value<br>                <SPAN style="color:#00007F">Case</SPAN> "Bloqueo"<br>                    <SPAN style="color:#00007F">Call</SPAN> JBACond1<br>                <SPAN style="color:#00007F">Case</SPAN> "Block"<br>                    <SPAN style="color:#00007F">Call</SPAN> JBACond1<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

 

[VoG]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$C$9" Then Exit Sub
Select Case Target.Value
    Case "Block":  Call JBACond2
    Case "Bloqueo": Call JBACond1
End Select
End Sub

 

[timfritsch]

Something like this should work:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$C$9" Then 
    If Target.value = "Bloqueo" then 
      Call JBACond1
    ElseIf target.value = "Block" then
      Call JBACond2
    End If
  End if
End Sub

Tim
End Sub

 

[PVA0724]

Hi All

First thank you for the quick turnaround.

Also now I'm looking for the solution proposed, I noted that I missed to explain something else.

In Cell C9 that trigger the case statement it's also another options so for example

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

So would it be possible to have the or statement to separate different cases that will trigger the same macro, where cell C9 has a Validation list with those values.

Again, thank you so much.

 

[shg]

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

 

[PVA0724]

It worked great. Thank you so much

 

[shg]

You're welcome.