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

 

[mikerickson]

Rather than using a userform, you could use Application.InputBox and let the user click on the column to sort.

Dim uiRange As Range

On Error Resume Next
Set uiRange = Application.InputBox("Select a range", Default:="$A$4", Type:=8)
Set uiRange = Application.Intersect(Range("A:G"), uiRange)
Set uiRange = uiRange.EntireColumn.Cells(4, 1)
On Error GoTo 0

If uiRange Is Nothing Then
    MsgBox "selection out of range (or canceled)"
    Exit Sub
End If

With Sheets("HONDA LIST")
    If .AutoFilterMode Then .AutoFilterMode = False
    x = .Cells(.Rows.Count, 1).End(xlUp).Row
    
    .Range("A3:G" & x).Sort Key1:=uiRange, Order1:=xlAscending, Header:=xlGuess
End With

 

[ipbr21054]

Sorry im confused.
How would i need to apply this.

Where is this applicateion box you mention.

Thanks

 

[ipbr21054]

I think i would just like to go the userform route.

Thanks

 

[mikerickson]

Take the code that I posed and paste in between

Application.ScreenUpdating=False

and

ActiveWorkbook.Save

in the code you posted. Replacing the code that you currently have.

If you do want to go the userform route, what have you got so far. What controls and what code.

 

[ipbr21054]

No code at present.

The userform is called AtoZHondaList

ComboBox1 is where the column selection will take place.

CommandButton1 when press will run the code to sort the column selected from A-Z.

Code below will advise Worksheet \ Range etc

Private Sub SortVehicleButton_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("B4"), Order1:=xlAscending, Header:=xlGuess
        
    End With
                      
    ActiveWorkbook.Save
    
    Application.ScreenUpdating = True
    Sheets("HONDA LIST").Range("B4").Select
    
End Sub

 

[mikerickson]

This assumes that the list is set up such that if the user selects the first item in the ListBox, then sort on column A. If the second item, then sort on column B, etc..

Private Sub SortVehicleButton_Click()
    
    Dim x As Long
    If ComboBox1.ListIndex = -1 Then Exit Sub: Rem nothing selected

    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").Offset(0, ComboBox1.ListIndex), Order1:=xlAscending, Header:=xlGuess
        
    End With
                      
    ActiveWorkbook.Save
    
    Application.ScreenUpdating = True
    Sheets("HONDA LIST").Range("B4").Select
    
End Sub

 

[ipbr21054]

What am i missing here ?
Im just lost.

Where does that code go as i would like to select a column from ComboBox1
Once selected i then press my CommandButton1 which will sort that selected column A-Z

I dont follow what you advised in the last post

 

[mikerickson]

You posted a sub that was in the userform's code module. I editied it. Put it back in the same place it came from.

 

[ipbr21054]

Ok,
Ive done that but when clicked nothing happens.

How should it work.