如何在使用VBA的用户条目后排序MS-Excel表列?

Question

我想在输入列范围内的项后为Excel表列添加自动排序功能。下面的脚本已经插入到所需的工作表中。

Private Sub WorksheetActivate()
' Sorts table automatically after each entry
Dim WKSeriesList    As Sort

Set WKSeriesList = ActiveSheet.ListObjects("KitchenLinesTable").Sort
WKSeriesList.SortFields.Clear
'Clear previous sorting method
With WKSeriesList
.SortFields.Add2 Key:=Range("KitchenLinesTable[[#All],[Kitchen Series]]"), _
SortOn:=xlSortOnValues, Order:=xlAscending
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

Answer 1

Worksheet_Change事件检测列'Kitchen Series'中的任何更改，并调用排序表作为一些参数的过程:表名，列名，排序类型。

将此代码放入表单模块

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("KitchenLinesTable[[#All],[Kitchen Series]]")) Is Nothing Then
Call sortTable("KitchenLinesTable", "Kitchen Series", xlAscending)
End If
End Sub

将此代码放在标准模块上

Sub sortTable(tblName As String, colName As String, sOrder As XlSortOrder)
Dim ol As ListObject: Set ol = ActiveSheet.ListObjects(tblName)
Dim olColRng As Range: Set olColRng = ol.ListColumns(colName).DataBodyRange

ol.Sort.SortFields.Clear

ol.Sort.SortFields.Add2 _
Key:=olColRng, _
SortOn:=xlSortOnValues, _
Order:=sOrder, _
DataOption:=xlSortTextAsNumbers

With ol.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub