Toggle Sort by selecting column (row, not header) or cell in table

[BG1983]

Hi, I am trying to figure out how to sort a table column by descending order [or ascending order if its already in descending order] by clicking either the column "Header" (my actual header is a hidden row because the names are not user friendly, so the user will see the row above the table and this is what they will be double clicking. The caveat here is that the table has columns with number, alpha-numberic, all alpha, currency, and dates... does anyone know how to accomplish this? I could also link a (Click) button to each cell header if that is easier but am trying to avoid that because then will need to edit each macro according to the data in that particular column. The example workbook attached is only some of the columns... in the actual workbook its around 70 columns with 10-15k rows so trying to keep this as lightweight as possible

Thanks for any help!

[Leith Ross]

Hello BG1983,

If I understood you needs correctly then the attached workbook should do what you want.

Here is the code that was added...

Sheet1 Selection Change Event Code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Target.Cells.Count > 1 Then Exit Sub
    
    If Not Intersect(Target, ActiveSheet.ListObjects(1).HeaderRowRange.Offset(-1, 0)) Is Nothing Then
        Call SortColumn
    End If
    
End Sub

Module1 Code

Sub SortColumn()

    Dim Cell        As Range
    Dim Rng         As Range
    Dim sortOrder   As Long
    Dim Tbl         As ListObject
        
        On Error Resume Next
            Set Tbl = ActiveSheet.ListObjects(1)
            If Err <> 0 Then
                MsgBox "There is no Table on this worksheet.", vbExclamation
                Exit Sub
            End If
        On Error GoTo 0
                
        Set Cell = Tbl.Range.Columns(ActiveCell.Column - Tbl.Range.Column + 1).Cells(1, 1)
                        
        With Tbl.Sort
            If .SortFields.Count > 0 Then
                sortOrder = .SortFields(1).Order
                If sortOrder = 1 Then
                    sortOrder = 2
                Else
                    sortOrder = 1
                End If
            Else
                sortOrder = xlAscending
            End If
            .SortFields.Clear
            .SortFields.Add Key:=Cell, Order:=sortOrder, SortOn:=xlSortOnValues, DataOption:=xlSortNormal
            .Header = Yes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .Apply
        End With
        
End Sub

[BG1983]

Awesome! Very much appreciated!

[Leith Ross]

Hello BG1983,

You're welcome. It is always nice to get it right the first time.