CODEWITHSUNDEEP
vbaexcel
Matty
Matty

Reputation: 29

Worksheet change ambiguous name solution?

I know that a single sheet can't have two independent subs named worksheet change. So I removed it.

I can't figure out the modification I require to make this code run though. Would love some help. 

Private Sub Worksheet_Change(ByVal Target As Range)


If Target.Row = 10 And Target.Column = 121 And Target.Count = 1 Then

Set Target = Range("range1")

If Target <> "Calculate" Then Exit Sub

Select Case Target.Value
Case "Calculate"
    macro1

End Select
End If


ElseIf Target.Row = 10 And Target.Column = 123 And Target.Count = 1 Then

Set Target = Range("range2")

If Target <> "Calculate" Then Exit Sub

Select Case Target.Value
Case "Calculate"
   macro2

End Select

End If

End If



End Sub

Upvotes: 0

Views: 71

Answers (1)

tigeravatar
tigeravatar

Reputation: 26640

Something like this should work for you. As a note, never try to reassign the Target argument:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rngCheck1 As Range
    Dim rngCheck2 As Range

    Set rngCheck1 = Intersect(Target, Me.Range("DQ10")) 'Row 10, Column 121
    Set rngCheck2 = Intersect(Target, Me.Range("DS10")) 'Row 10, Column 123

    Application.EnableEvents = False
    On Error GoTo CleanExit

    If Not rngCheck1 Is Nothing Then
        If Trim(LCase(rngCheck1.Value)) = "calculate" Then Call macro1
    End If

    If Not rngCheck2 Is Nothing Then
        If Trim(LCase(rngCheck2.Value)) = "calculate" Then Call macro2
    End If

CleanExit:
    Application.EnableEvents = True

End Sub

Upvotes: 1

Related Questions