Running multiple 'Private Sub Worksheet_Change(ByVal Target As Range)' in one worksheet

Question

I have tried to merge 2 codes into one Private Sub and while the first one runs fine the second is not being pick up at all. It does not return any errors it just does not call the required Sub. Any help will be appreciated.

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo justenditall
    Application.EnableEvents = False


    If Not Intersect(Target, Range("e6:e1000, M6:m1000")) Is Nothing Then
        If Target.Value <> "" Then
        ActiveSheet.Unprotect Password:="password"
        Target.Locked = True
        ActiveSheet.Protect Password:="password"
        End If

    Next

    ElseIf Not Intersect(Target, Range("P1")) Is Nothing Then
        If Target.Value = 1 Then
        Call SetRecipients
        End If
    Next

justenditall:
    Application.EnableEvents = True
End Sub

Siddharth Rout · Answer 1 · 2014-02-09T11:13:13.920

1

Your code has Next which is not required. And you are missing End If. I am surprised that the code is running at all to execute the first IF/ENDIF

This works for me (Tried and Tested)

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo justenditall
    Application.EnableEvents = False

    If Not Intersect(Target, Range("e6:e1000, M6:m1000")) Is Nothing Then
        If Target.Value <> "" Then
            ActiveSheet.Unprotect Password:="password"
            Target.Locked = True
            ActiveSheet.Protect Password:="password"
        End If
    ElseIf Not Intersect(Target, Range("P1")) Is Nothing Then
        If Target.Value = 1 Then
        Call SetRecipients
        End If
    End If
LetsContinue:
    Application.EnableEvents = True
    Exit Sub
justenditall:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

Sub SetRecipients()
    MsgBox "Second One Runs"
End Sub

edited Feb 09 '14 at 11:13

answered Feb 09 '14 at 11:05

Siddharth Rout

147,039
17
206
250

Thanks a lot for your reply. Everything works fine except cell P1 contains the following formula =SUMIF(R6:R1000,">0") The macro does not pick up when this cell changes from zero to one or upwards. If I manually type 1 into P1 then everything works fine. – user3111354 Feb 09 '14 at 12:31
You can try `If Evaluate(Target.Formula) >= 1 Then`. I don't know why `.Value` fails. Ask @Siddharth Rout when he comes back :). Not sure either if it has something to do with the function `Sumif`. No way to test atm. – L42 Feb 09 '14 at 15:52
@user3111354: Posting from phone : formula changes can only be captured by worksheets calculate event. I have already answered similar questions in the past... Do a search or wait till I come back. Should be another 1 hour and then I will share the links :) – Siddharth Rout Feb 09 '14 at 18:35
@user3111354: See [THIS](http://stackoverflow.com/questions/11406628/vba-code-doesnt-run-when-cell-is-changed-by-a-formula) – Siddharth Rout Feb 09 '14 at 19:11
That link has helped and i now have the macro working. However, it now runs Call SetRecipients whenever the value in P1 is changed up or down where as i only want it to run if the value is 1 or above. – user3111354 Feb 09 '14 at 20:28
@user3111354: In the calculate event, you need to use something like this `If Range("A1").Value <> PrevVal and Range("A1").Value >= 1 Then` – Siddharth Rout Feb 09 '14 at 20:31
That appears to have worked although i have mad one slight modification by removing the < from the formula. Many thanks for your help and advise. If Range("A1").Value > PrevVal and Range("A1").Value >= 1 Then – user3111354 Feb 10 '14 at 14:10

Running multiple 'Private Sub Worksheet_Change(ByVal Target As Range)' in one worksheet

1 Answers1