VBA to taking combobox output to select the sort key

I want the user to be able to sort a field by choosing the column title from a combobox.  I have a vertical list of the column titles and the associated number (0-16)  in an array that I can access with vlookup and can access the header with hlookup, but can’t get the column designation into the sortkey of the macro

Contributor Asked on January 1, 2017 in VBA: General.
Add Comment
3 Answer(s)

Hi,

Here is the set of instructions to sort data based on header selection from the combobox using VBA

1.  Go to VBA editior by clicking on Alt+F11.

 2. Insert Label , Combo box and command button.

 3.Change the cation of the label to “Select Header to Sort Data”.

 4.Change the caption of the CommandButton to “Sort Data”.

 5. Here is the design of the screen shot for your reference.

Sort Data using Combobox

 

6. Double click on the Userform and add the following code.

Private Sub UserForm_Initialize()
'Populate Color combo box.
Dim rngHdr As Range
Dim ws As Worksheet
Dim HdrRange As Range
Set HdrRange = Range("A1:Z1")
Set ws = Sheets("Sheet1")
For Each rngHdr In HdrRange
Me.ComboBox1.AddItem rngHdr.Text
Next rngHdr
End Sub

7. Double click on the command button and add the following code.

Private Sub CommandButton1_Click()
'Here Range("A1:E20") is target range to sort
KeyToSort = ComboBox1.Text
Sheets("Sheet1").Range("A1:F20").Sort Key1:=KeyToSort, Header:=xlYes
End Sub

 

8. Go to worksheet,  Insert shape from the illustration group.

9. Right click on the UserForm, click on edit text as “Show Userform”.

Here is the screen shot for your reference.

Sort Data using Combobox

11. Right click on the shape, click on assign macro.

12. Click on ‘New’ from Assign Macro window and add the following code.

UserForm1.Show

13. Now, click on the ‘Show userform’ from the Worksheet.

14. Select header from the ComboBox and click on Sort data .

15. You can see the output on the worksheet.

Example: I want to sort data based on 2nd column. I have selected Hdr2 from the ComboBox and clicked on Sort Data button.

Now, you can see the following output. data has sorted based on 2nd column.

Sort Data using Combobox

 

Hope, It clarifies your query.

 

Regards-Valli.

 

 

 

 

 

 

 

 

Expert Answered on January 3, 2017.
Add Comment

You can find related information in the specified link for more information.

VBA Sort Data with headers Excel Example Macro Code

Regards-Valli

Expert Answered on January 3, 2017.
Add Comment

Thanks for your response.  I’m new to VBA but not to Excel so in the interim I came across the following solution. (I should add that the data populating the table is based on formulae from other sheets in the book and so is protected and the macro unprotects and reprotects the worksheet).

I used a combo box to list the column headers vertically and gave each its corresponding number and its cell address in adjacent cells.  Then used the Combo box output cell to define the cell address via a vlookup function.  A button then starts the macro which reads that address as a variable and inserts as the rangekey.  I like the idea of a single box instead of a combo box and then a button and I ma going to experiment with your code to help me learn more about VBA.

Thanks again

Contributor Answered on January 4, 2017.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.