Ms Excel VBA: Set a sorting combo box

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.

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 ch开发者_运维百科oose 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.

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

(Keeping columns S and R visible for this example)

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.

First thing, if you haven't already done so, is add the Developer Tab to 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 Tab and 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.)

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

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

The input range range should be the cells containing your sorting options. It look something like this: 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

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

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.