Search code examples
vbaexcelexcel-2013

Is it possible to delay the Worksheet_Change code? Excel Vba


I am filling some data validation dropdown lists, their values are in a table.
I have a buttons (that link to userforms) for the users to add items to the table. After a new value is entered the Worksheet_Change code for the sheet sorts the table.
So if a value is deleted the table resizes.

But now my problem is when the button in the userform is clicked first a row gets added to the table and then the value gets added to that row. Before the value is added the Worksheet_Change has allready detected the new empty row and deleted it.
Is it possible to delay this, or does someone know a better solution?

The code for the userform:

Private Sub butAddProject_Click()

    Dim listSheet As Worksheet
    Dim listTable As listObject
    Dim newRow As ListRow
    Dim ProjectName As String

    ProjectName = txtAddProject.Text

    Set listSheet = Sheets("Projects-Tasks")
    Set listTable = listSheet.ListObjects(1)

    If ProjectName <> "" Then
        Set newRow = listTable.ListRows.Add
        newRow.Range(1, 1).Value = ProjectName
    Else
        MsgBox "Enter a project name first!"
    End If

    txtAddProject.Text = ""
    formAddProject.Hide

End Sub

And finally the code for the Worksheet_Change:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim ws As Worksheet
    Dim strList As String
    Set ws = Sheets("Projects-Tasks")
    strList = Cells(2, Target.Column).listObject.Name

    If strList <> "" Then
        Application.ScreenUpdating = False
            With ListObjects(strList).Sort
                .SortFields.Add _
                    Key:=Cells(3, Target.Column), _
                    SortOn:=xlSortOnValues, _
                    Order:=xlAscending
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With

        With ws.ListObjects(strList)
            .Resize .DataBodyRange.CurrentRegion
        End With

    End If

    Application.ScreenUpdating = True

End Sub

thanks in advance!


Solution

  • Turn events off while adding the new row:

    If ProjectName <> "" Then
        application.enableevents = False
        Set newRow = listTable.ListRows.Add
        application.enableevents = True
        newRow.Range(1, 1).Value = ProjectName
    Else
    

    I assume you still want it to sort when you add the new value so I reset events before the line that adds the new value.