Ms Excel VBA：设置排序组合框

Ms Excel VBA: Set a sorting combo box

提问by Jorge

I am new to VBA although I have some experience in Visual Basic. I have a Microsoft Excel 2010 worksheet. Row 29 has the headers of a table and the data is from row 30 and so on. This table has like 20 columns.

尽管我对 Visual Basic 有一些经验，但我还是 VBA 新手。我有一个 Microsoft Excel 2010 工作表。第 29 行有一个表的标题，数据来自第 30 行，依此类推。该表有 20 列。

I'm trying to insert a combo in this Worksheet with three options, so when you choose the first, it will apply descending sorting to all the table according to column R and then column S. If you choose the second, it will apply descending sorting according to column S and then column R. If you choose the first it will apply descending sorting according to column A. Column S and Column R will be hidden. I hope you guys can help me out. Thank you and sorry for my English.

我正在尝试在此工作表中插入一个包含三个选项的组合，因此当您选择第一个时，它将根据 R 列和 S 列对所有表应用降序排序。如果您选择第二个，它将应用降序根据列 S 排序，然后列 R。如果您选择第一个，它将根据列 A 应用降序排序。列 S 和列 R 将被隐藏。我希望你们能帮助我。谢谢你，对不起我的英语。

回答by ray

Just to put a picture to words, I assume you have an excel sheet that looks similar like this:

只是为了把图片放在文字上，我假设你有一个看起来像这样的excel表：

(Keeping columns S and R visible for this example)

（在此示例中保持列 S 和 R 可见）

You want to add a combo box that will sort columns based on the value selected in the combo box that will be like so:

您想要添加一个组合框，它将根据组合框中选择的值对列进行排序，如下所示：

• Option 1: Sort descending on column R, then S
• Option 2: Sort descending on column S, then R
• Option 3: Sort descending on column A.

• 选项 1：在列 R 上降序排序，然后是 S
• 选项 2：在列 S 上降序排序，然后是 R
• 选项 3：按 A 列降序排序。

First thing, if you haven't already done so, is add the Developer Tab to Excel.

首先，如果您还没有这样做，请将Developer Tab添加到 Excel。

Next, put cells from the table in a Named Range. If the rows in this table will change, then make sure you create a dynamic named range. (Dynamic Named Ranges are a little tricky, but very useful for dynamic data)

接下来，将表格中的单元格放入命名区域。如果此表中的行将更改，请确保创建动态命名范围。（动态命名范围有点棘手，但对动态数据非常有用）

Add the combo box by clicking Insert from the Developer Taband select combo box from Form Controls(NOTE:An ActiveX combobox is a completely different type of control. You could come to the same result using it, but the code would be different.)

通过单击“开发人员”选项卡中的“插入”并从“表单控件”中选择组合框来添加组合框（注意：ActiveX 组合框是一种完全不同类型的控件。使用它可以得到相同的结果，但代码会有所不同。）

Drag the combobox somewhere on the worksheet:

将组合框拖动到工作表上的某处：

Now add the options values to the combo. You should go somewhere in your workbook and add the values for you combo box (e.g. Sheet2, Cells A1, A2 & A3).

现在将选项值添加到组合中。您应该在工作簿中的某个位置为您的组合框添加值（例如 Sheet2、单元格 A1、A2 和 A3）。

Return to your sheet where the table and combo box reside. Right-click on the combo box and select Format Control.

返回到表格和组合框所在的工作表。右键单击组合框并选择Format Control。

The input range range should be the cells containing your sorting options. It look something like this: Sheet2!$A$1:$A$3

输入范围应该是包含排序选项的单元格。它看起来像这样： Sheet2!$A$1:$A$3

Right click on the combo box again and select Assign Macro. Give the Macro a name and put the Macro in This Workbook

再次右键单击组合框并选择Assign Macro。为宏命名并将宏放在此工作簿中

Click New. You will be taken to the Visual Basic Editor.

单击新建。您将进入 Visual Basic 编辑器。

Here you can apply your sorting code:

您可以在此处应用排序代码：

Option Explicit

    Sub DropDown2_Change()
        Dim comboValue As String
        Dim Key1ColumnIndex As Integer
        Dim Key2ColumnIndex As Integer

        'You can get the name by doing something like this in the immediate window:  "? Sheet1.Shapes(1).OLEFormat.Object.Name"
        comboValue = Sheet1.Shapes("Drop Down 2").ControlFormat.List(Sheet1.Shapes("Drop Down 2").ControlFormat.ListIndex)

        Select Case comboValue

            Case "Option1"
                Key1ColumnIndex = 18
                Key2ColumnIndex = 19
            Case "Option2"
                Key1ColumnIndex = 19
                Key2ColumnIndex = 18
            Case "Option3"
                Key1ColumnIndex = 1
                Key2ColumnIndex = 1
        End Select


       Range("DataValues").Sort Key1:=Range("DataValues").Cells(1, Key1ColumnIndex), _
                                Order1:=xlDescending, Header:=xlNo, DataOption1:=xlSortNormal, _
                                Key2:=Range("DataValues").Cells(1, Key2ColumnIndex), order2:=xlDescending
    End Sub

You should be now be good to go. If you need a working example, take a look at the sample sheet I created here; note that it does not have a dynamic name range for the table.

你现在应该可以走了。如果您需要一个工作示例，请查看我在此处创建的示例表；请注意，它没有表的动态名称范围。