CODEWITHSUNDEEP
excelvba
Steeletc4
Steeletc4

Reputation: 1

Cannot combine two Private Sub Worksheet_Change(ByVal Target As Range)

I am trying to combine two Private Sub Worksheet_Change(ByVal Target As Range) into the same worksheet, but the first one only works, the second one doesn't. I receive no errors from debugging - so it has to be a simple tweak I'm guessing. Below is the code - thank you in advance for helping me.

Private Sub Worksheet_Change(ByVal Target As Range)

    'Auto Re-sort of Entire entry based on change in Due Date

    Dim Table As ListObject
    Dim SortCol As Range

    Set Table = ActiveSheet.ListObjects("Table1")
    Set SortCol = Range("Table1[Due Date]")

    If Not Intersect(Target, SortCol) Is Nothing Then
        With Table.Sort
            .SortFields.Clear
            .SortFields.Add Key:=SortCol, Order:=xlAscending
            .Header = xlYes
            .Apply
        End With
    End If

    'Automating the Move Entire Row Based on Value being "Completed", from Tasks 
    worksheet to Completed Worksheet

    Dim Z As Long
    Dim xVal As String
    On Error Resume Next
        If Intersect(Target, Range("H:H")) Is Nothing Then
        Application.EnableEvents = False
        For Z = 1 To Target.Count
            If Target(Z).Value > 0 Then
                Call MoveBasedOnValue
            End If
        Next
        Application.EnableEvents = True
        End If
End Sub

Upvotes: 0

Views: 305

Answers (2)

Ali Alahverdi
Ali Alahverdi

Reputation: 1

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A2:A10000")) Is Nothing Then
    If Target.Value <> "" Then
        With Target(1, 2)
             .Value = m2i(Date)
             .EntireColumn.AutoFit
        End With
        With Target.Offset(0, -4)
        .Value = Target.Offset(-1, -4) + 1
        .EntireColumn.AutoFit
    End With
    Else
        With Target(1, 2)
             .Value = ""
             .EntireColumn.AutoFit
        End With
        With Target.Offset(0, -4)
                .Value = ""
                .EntireColumn.AutoFit
        End With
    End If
End If
End Sub

Upvotes: -1

alexo
alexo

Reputation: 89

Maybe I’m not understanding well your issue but… why don’t you just put one Private Sub Worksheet_Change(ByVal Target As Range)? Then, have it calling as many other functions as you want:

Private Sub Worksheet_Change(ByVal Target As Range)
     sub1 Target

     sub2 Target
End Sub

Private Sub sub1(ByVal Target As Range)
    'Replace with the code of your first function:
    MsgBox "Hello from sub1"
End Sub

Private Sub sub2(ByVal Target As Range)
    'Replace with the code of your second function:
    MsgBox "Hello from sub2"
End Sub

Upvotes: -1

Related Questions