Userform to select which column to sort

[ipbr21054]

Afternoon,

Below is the code currently in use to sort column A in A-Z order

The downside is that i also have the same code repeated on the sheet to sort column B,C,D,E,F & G
So as you can imagine my page has a long lists of the same code but with the column letter changed.

So i am looking for a code to use on a userform.

On the userform would be a drop down list with the column names in & a command button.
It should work by the user selecting the column name they require sorting then the command button would carry out the sort A-Z.

If possible it would be nice to have in the drop down list the actual name of the column example "VIN" as apposed to the column letter A

Have a nice day.

Private Sub SortVinButton_Click()
    
    Dim x As Long
    
    Application.ScreenUpdating = False
    
    With Sheets("HONDA LIST")
        If .AutoFilterMode Then .AutoFilterMode = False
        x = .Cells(.Rows.Count, 1).End(xlUp).Row
        
        .Range("A3:G" & x).Sort Key1:=Range("A4"), Order1:=xlAscending, Header:=xlGuess
    
    End With
                      
    ActiveWorkbook.Save
    
    Application.ScreenUpdating = True
    Sheets("HONDA LIST").Range("A4").Select
    
End Sub

 

[ipbr21054]

The code i supplied was on a command button on the sheet.
Once pressed the column in question would sort.

Using that same button but with the code you advised i click it but thats it.

How is the userform supposed to open for the user to select the column they require sorting ?

 

[ipbr21054]

Supplied is the userform.

Im looking to select the dropdown.
Select the named column
Press the commandbutton to sort the column just selected A-Z

 

[mikerickson]

Is anything selected in the combo box?
How does the combobox get its values?

 

[ipbr21054]

When you asked for code i mentioned nothing at present.
We need to load the combobox with column names from A-G then apply the sort code to the commandbutton1

 

[ipbr21054]

This should populate the combobox.
Can’t check as out at present.

Private Sub UserForm_Initialize()
Dim Cell As Range
For Each Cell In Range("A3:G3")
    Data_UF.CB.AddItem (Cell.Value)
Next Cell 
End Sub

 

[ipbr21054]

Any advice please for once the column is selected how we then sort it’s column on the worksheet

 

[mikerickson]

The code you provided should sort those rows in columns A:G based on the column chosen.

It almost sounds like you have this bit of code, that bit of code, but haven't put them all together to see how the userform works.

 

[ipbr21054]

I see that the combobox will be populated using the code in post #15.
The code to sort A-Z is what I had shown in post #1 BUT my issue is the code to say hey take note of the header the user selected in the combobox now go to that column and then do the following. This would
Then this would be the code in post #1

 

[ipbr21054]

Morning,
I have used the following code to populate the ComboBox with the column Headings.

Private Sub UserForm_Initialize()
Dim rheadings As Range
Dim cl As Range
Set rheadings = Worksheets("HONDA LIST").Range("A2:G2")
For Each cl In rheadings
Me.ComboBox1.AddItem cl.Value
Next cl
End Sub

Where i need some assistance now please would be once the item has been selected from the combobox we should press the CommandButton1 to sort that column A to Z

Using the code below as an exmple of which works perfect to sort column B in A-Z order & looking for last row.

BUT what i am now missing is basically the link between the ComboBox1 selection & sorting that column after Commandbutton1 being pressed.

So it should work like so,
Range is A3:G3
Look at selection made from combobox.
Find last row in selected column
Sort A to Z

    Dim x As Long
    
    Application.ScreenUpdating = False
    
    With Sheets("HONDA LIST")
        If .AutoFilterMode Then .AutoFilterMode = False
        x = .Cells(.Rows.Count, 1).End(xlUp).Row
        
        .Range("A3:G" & x).Sort Key1:=Range("B4"), Order1:=xlAscending, Header:=xlGuess
        
    End With
                      
    ActiveWorkbook.Save
    
    Application.ScreenUpdating = True
    Sheets("HONDA LIST").Range("B4").Select

 

[ipbr21054]

Hi,
Many Thanks all completed.
Point noted about showing sheet in use etc.