vba 如何使 Microsoft Excel 中的两个按钮在单击时对表格进行排序？

How to make two buttons in Microsoft Excel sort a table upon click?

提问by Simon Ayzman

I have a spreadsheet with 5 columns (A-E) with names below:

我有一个包含 5 列 (AE) 的电子表格，名称如下：

1. "TEST YEAR"
2. "FORM"
3. "QUESTION"
4. "TAGS"
5. the fifth has no header title, but it still has data.

1. “测试年”
2. “形式”
3. “题”
4. “标签”
5. 第五个没有标题标题，但它仍然有数据。

I have a separate feature in the spreadsheet that searches through the TAGS column for text and fills in the cells of a row blue if there is a match. Since I might be doing various searches at different times, I want to have two buttons that--upon clicking--will sort the 5 columns.

我在电子表格中有一个单独的功能，可以在 TAGS 列中搜索文本，并在匹配时填充蓝色行的单元格。由于我可能会在不同时间进行各种搜索，因此我希望有两个按钮——点击后——将对 5 列进行排序。

The first button will simply be a default sort that brings it back to normal; it sorts the table by TEST YEAR, then by FORM, then by QUESTION. The second button sorts the table first by cell color (blue), then by TEST YEAR, then by FORM, then by QUESTION.

第一个按钮将只是一个默认排序，使其恢复正常；它按 TEST YEAR 对表格进行排序，然后是 FORM，然后是 QUESTION。第二个按钮首先按单元格颜色（蓝色）对表格进行排序，然后是 TEST YEAR，然后是 FORM，然后是 QUESTION。

What would such sorting macros look like, and how would I run them by clicking on buttons?

这样的排序宏会是什么样子，我将如何通过单击按钮来运行它们？

采纳答案by excelVBAmaster.com

The code for your first sort is:

第一种排序的代码是：

Sub Sort_BackToNormal()

If Sheets(1).AutoFilterMode = False Then
    Sheets(1).Range("A1:E1").AutoFilter
End If

RowCount = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
ActiveWorkbook.Worksheets(1).AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets(1).AutoFilter.Sort.SortFields.Add Key:=Range("A2:A" & RowCount), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets(1).AutoFilter.Sort.SortFields.Add Key:=Range("B2:B" & RowCount), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets(1).AutoFilter.Sort.SortFields.Add Key:=Range("C2:C" & RowCount), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets(1).AutoFilter.Sort
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

End Sub

The code for the second sort is:

第二种排序的代码是：

Sub Sort_Color()

If Sheets(1).AutoFilterMode = False Then
    Sheets(1).Range("A1:E1").AutoFilter
End If

RowCount = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
ActiveWorkbook.Worksheets(1).AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets(1).AutoFilter.Sort.SortFields.Add(Range("D1"), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(0, 112, 192)
ActiveWorkbook.Worksheets(1).AutoFilter.Sort.SortFields.Add Key:=Range("A2:A" & RowCount), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets(1).AutoFilter.Sort.SortFields.Add Key:=Range("B2:B" & RowCount), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets(1).AutoFilter.Sort.SortFields.Add Key:=Range("C2:C" & RowCount), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets(1).AutoFilter.Sort
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

ActiveWorkbook.Worksheets(1).AutoFilter.Sort.SortFields.Add(Range("D1"), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(0, 112, 192)
End Sub

To make a button:

制作按钮：

1. Insert a shape
2. Make it pretty
3. Right-click the shape and assign Macro.

1. 插入形状
2. 让它漂亮
3. 右键单击形状并指定宏。