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!
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.