Multiple Sub Worksheet_Change(ByVal Target As Range)

[Nick70]

Hi,

I have a macro Sub Worksheet_Change(ByVal Target As Range) which runs when cell A4 is changed see code below:

Sub worksheet_change(ByVal target As Range)
Set target = Range("A4")
If target.Value = "Escalation Form" Then
 Call Expand
End If
If target.Value = "Relationship Profile Summary Form" Then
Call Collapse
End If
End Sub

I would now like to add another condition so that if cell B8 changes then some rows are hidden.

Code would look like one below:

Sub worksheet_change(ByVal target As Range)
Set target = Range("B8")
If target.Value = "Counterparty" Then
Call Hide
End If
If target.Value = "Client" Then
Call Unhide
Call Paste
End If
If target.Value = "Business Partner" Then
Call Unhide
Call Paste
End If
End Sub

How can I combine these two pieces of code?

If I add second one I get Compile error: "Ambiguous name detected: worksheet_change"

Many Thanks,
Nic

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Address(0, 0) = "A4" Then
      If Target.Value = "Escalation Form" Then
         Call Expand
      ElseIf Target.Value = "Relationship Profile Summary Form" Then
         Call Collapse
      End If
   ElseIf Target.Address(0, 0) = "B8" Then
      If Target.Value = "Counterparty" Then
         Call Hide
      ElseIf Target.Value = "Client" Then
         Call Unhide
         Call Paste
      ElseIf Target.Value = "Business Partner" Then
         Call Unhide
         Call Paste
      End If
   End If
End Sub

 

[Yongle]

Try something like this

Sub Worksheet_change(ByVal Target As Range)
    Dim T As String:    T = Target.Value
    If Target.Address(0, 0) = "A4" Then
        Select Case T
            Case "Escalation Form"
                Call Expand
            Case "Relationship Profile Summary Form"
                Call Collapse
        End Select
    ElseIf Target.Address(0, 0) = "B8" Then
        Select Case T
            Case "Counterparty"
                Call Hide
            Case "Client", "Business Partner"
                Call Unhide
                Call Paste
        End Select
    End If
End Sub

 

[Yongle]

I think this should also work

Sub Worksheet_Change(ByVal Target As Range)
    Select Case Target.Address(0, 0) & Target.Value
        Case "A4Escalation Form"
            Call Expand
        Case "A4Relationship Profile Summary Form"
            Call Collapse
        Case "B8Counterparty"
            Call Hide
        Case "B8Client", "B8Business Partner"
            Call Collapse
            Call Paste
    End Select
End Sub

 

[Nick70]

Great thanks a lot!
Nic
? ?

 

[Fluff]

Glad we could help & thanks for the feedback