HI, Please find the following link to improve & optimize your VBA ccode. http://analysistabs.com/vba/optimize-code-run-macros-faster/ 1. Do save as your file...
hi, give this a try. Cell A1 will contain 120105, Cell G2 will contain the answer 1215
Hi,Here is the solution to disable message and to automatically update the links when you open a workbook. Please follow...
Hi, Here is the sample projects related to userform available in the following link. You can also download sample workbook...
Hi, Here is the solution to refer First name from one cell and Last name from another cell. You can...
Hi,Here is the solution to generate multiple XML files using VBA code. Screenshots:Here are the sample input & output screen shots...
Thank you for your help as also, much appreciated.
HI, Please run 'Sheets_VeryHidden ' procedure before clicking on show userform. So that you will not see specified worksheets in...
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
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.
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
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
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.
11. Right click on the shape, click on assign macro.
12. Click on ‘New’ from Assign Macro window and add the following code.
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.
Hope, It clarifies your query.
You can find related information in the specified link for more information.
VBA Sort Data with headers Excel Example Macro Code
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.
Please share using the share button above.
If you found the answer is best answer for your question, Please mark as 'best answer' by clicking on the right tick mark icon at the left side of the answer.
Found the answer useful and wants to credit the user, then vote the answer (vote up).