VBA to sort table by ´InputBox´ - enter or select the column header name which it will then sort by.

[novabond]

hello.
I have inherited the below code, which works great but, ideally i would like the code to ask the user ´which column header to sort the table by´.....
which the user can either type manually or, if possible select from a list?
you will notice i have made the bit below, that i would like to be a InputBox, bigger and in red... I hope my question make sense..
I am looking forward to reading what you think... as i have spent quite a while trying to search for this myself but, cant seem to find what i am looking for easily online already...
TIA

Sub SortPasswordCategorys2()
Dim iSheet As Worksheet
Dim iTable As ListObject
Dim iColumn As Range
Set iSheet = ActiveSheet
Set iTable = iSheet.ListObjects("TBL_other_passwords")
Set iColumn = Range("TBL_other_passwords[Category]")
With iTable.Sort
.SortFields.Clear
.SortFields.Add Key:=iColumn, SortOn:=xlSortOnValues, Order:=xlAscending
.Header = xlYes
.Apply
End With
End Sub

 

[Legacy 143009]

Make a validation list from the headers of table like the image below. Sort button macro will be:

Sub sort_button()
    With Worksheets("Sheet1")
    .ListObjects("TBL_other_passwords").Sort. _
        SortFields.Clear
    .ListObjects("TBL_other_passwords").Sort. _
        SortFields.Add2 Key:=Range("TBL_other_passwords[[#All],[" & .Range("E1").Value & "]]"), SortOn:= _
        xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    End With
    With Worksheets("Sheet1").ListObjects("TBL_other_passwords"). _
        Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

 

[John_w]

Another approach using Application.InputBox with Type:=8 to allow the user to select a cell in the column to sort by.

Sub SortPasswordTable()

    Dim iTable As ListObject
    Dim iColumn As Range
    Dim sortColumn As Range
   
    Set iTable = ActiveSheet.ListObjects("TBL_other_passwords")
   
    Do
        Set sortColumn = Nothing
        On Error Resume Next
        Set sortColumn = Application.InputBox(Prompt:="Please select any cell in the table column you want to sort by", Type:=8)
        On Error GoTo 0
        If sortColumn Is Nothing Then Exit Sub
        If Intersect(iTable.Range, sortColumn(1)) Is Nothing Then
            MsgBox "First selected cell is outside the table", vbExclamation
        End If
    Loop While Intersect(iTable.Range, sortColumn(1)) Is Nothing
   
    Set iColumn = iTable.ListColumns(sortColumn.Column).Range
    With iTable.Sort
        .SortFields.Clear
        .SortFields.Add Key:=iColumn, SortOn:=xlSortOnValues, Order:=xlAscending
        .Header = xlYes
        .Apply
    End With
   
End Sub

 

[novabond]

Make a validation list from the headers of table like the image below. Sort button macro will be:

Sub sort_button()
    With Worksheets("Sheet1")
    .ListObjects("TBL_other_passwords").Sort. _
        SortFields.Clear
    .ListObjects("TBL_other_passwords").Sort. _
        SortFields.Add2 Key:=Range("TBL_other_passwords[[#All],[" & .Range("E1").Value & "]]"), SortOn:= _
        xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    End With
    With Worksheets("Sheet1").ListObjects("TBL_other_passwords"). _
        Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

View attachment 101953

Make a validation list from the headers of table like the image below. Sort button macro will be:

Sub sort_button()
    With Worksheets("Sheet1")
    .ListObjects("TBL_other_passwords").Sort. _
        SortFields.Clear
    .ListObjects("TBL_other_passwords").Sort. _
        SortFields.Add2 Key:=Range("TBL_other_passwords[[#All],[" & .Range("E1").Value & "]]"), SortOn:= _
        xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    End With
    With Worksheets("Sheet1").ListObjects("TBL_other_passwords"). _
        Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

View attachment 101953

works great thank you!

 

[novabond]

Another approach using Application.InputBox with Type:=8 to allow the user to select a cell in the column to sort by.

Sub SortPasswordTable()

    Dim iTable As ListObject
    Dim iColumn As Range
    Dim sortColumn As Range
  
    Set iTable = ActiveSheet.ListObjects("TBL_other_passwords")
  
    Do
        Set sortColumn = Nothing
        On Error Resume Next
        Set sortColumn = Application.InputBox(Prompt:="Please select any cell in the table column you want to sort by", Type:=8)
        On Error GoTo 0
        If sortColumn Is Nothing Then Exit Sub
        If Intersect(iTable.Range, sortColumn(1)) Is Nothing Then
            MsgBox "First selected cell is outside the table", vbExclamation
        End If
    Loop While Intersect(iTable.Range, sortColumn(1)) Is Nothing
  
    Set iColumn = iTable.ListColumns(sortColumn.Column).Range
    With iTable.Sort
        .SortFields.Clear
        .SortFields.Add Key:=iColumn, SortOn:=xlSortOnValues, Order:=xlAscending
        .Header = xlYes
        .Apply
    End With
  
End Sub

works great too, thank you!... one thing though.... my table starts in col B, and i think this code needs the table to start in col A.... as it currently sorts the next col to the one i select in my table... where would you update the code so it starts from row B?... but, brilliant code... thanks so much

 

[John_w]

one thing though.... my table starts in col B, and i think this code needs the table to start in col A.... as it currently sorts the next col to the one i select in my table... where would you update the code so it starts from row B?

Sorry, I should have catered for the table's starting column in the first place.

Change the Set iColumn = line to:

    Set iColumn = iTable.ListColumns(sortColumn.Column - iTable.Range(1).Column + 1).Range

 

[novabond]

Sorry, I should have catered for the table's starting column in the first place.

Change the Set iColumn = line to:

    Set iColumn = iTable.ListColumns(sortColumn.Column - iTable.Range(1).Column + 1).Range

thank you!