VBA Multiple Worksheet Change Events

Hi All,

Is anyone able to help me rework the below code so I can run these two macros (or More) within the same sheet. They are coflicting and a message saying an ambiguous name is detected appears.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("M1")) Is Nothing Then
Application.EnableEvents = False

      If Target = "HOME" Then
            Macro1
      ElseIf Target = "PRINT PAGE" Then

      ElseIf Target = "" Then

      ElseIf Target = "" Then

      End If
End If
Application.EnableEvents = True
Exit Sub


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim c As Range, i As Long
    On Error Resume Next
    Set c = Intersect(Target, Columns(1))
    If c Is Nothing Then Exit Sub
    If IsEmpty(c.Offset(-1, 0)) Or Not IsEmpty(c.Offset(1, 0)) Then Exit Sub
    i = c.Row
    If i > 1 Then
        Application.EnableEvents = False
        Range("G" & i - 1).Copy Range("G" & i)
        Application.EnableEvents = True
    End If
    On Error GoTo 0
End Sub

Any help is appreciated.

Cheers

Adam

Hi Mudraker,

I am not very good with VBA! How would I combine such code to run off different cell changes?

Cheers

zimbo

I think mudraker has given you an idea on how to comnbine the code.

I think what you need to do is actually give us an idea, in words, what the code is actually meant to do.

Hi Norie,

In the example shown the first half of the code is for an in cell drop down which when selected will run one of three macros(only one shown). The second part of the code copies the formula's from the previous row in the sheet to the new line being entered (the excel auto formula copy isn't working even though it is selected - note my previous thread).

I also have another sheet in the same workbook with two cells needing to run seperate macro's on change.

Cheers

Adam

Hi Norie,

Is there any way a Worksheet change event can be written so that I can for example have cell A1 run Macro 1, cell A2 run Macro 2, range A3:B10 run macro 3, etc when each are altered?

Cheers

Adam

Thanks Mudraker,

That works perfectly.

Cheers

Adam

Originally Posted by mudraker

Private Sub Worksheet_Change(ByVal Target As Range)
   Application.EnableEvents = False
   
   If Not Intersect(Target, Range("A1")) Is Nothing Then
      Macro1
   ElseIf Not Intersect(Target, Range("A2")) Is Nothing Then
      Macro2
   ElseIf Not Intersect(Target, Range("A3:b10")) Is Nothing Then
      Macro3
   End If
   Application.EnableEvents = True
End Sub

^ This really helped me out too, Mudraker. Thanks!