Multiple Sub Worksheet_Change(ByVal Target As Range)

Nick70

Board Regular
Joined
Aug 20, 2013
Messages
194
Hi,

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

VBA Code:
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:

Code:
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
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,682
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
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

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Try something like this
VBA Code:
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
 
Last edited:

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
I think this should also work
VBA Code:
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,682
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,127,865
Messages
5,627,344
Members
416,242
Latest member
Kas O

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top