Sort Data When a Header Is Clicked

[Excel Fox]

Remove ALL the macros in both the sheet, as well as in the modules. Copy the below code to ThisWorkbook module. You can now add any number of sheets, and the right-click controls will be available.

Also, the headerRow will now be available in the entire module, and just need to pass value to it in the AddOnRightClick() routine

Option Explicit

Dim HeaderRow As Long

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    
    AddOnRightClick
    
End Sub

Private Sub DeleteOnRightClick()

    On Error Resume Next
    With Application
        .CommandBars("Cell").Controls("Sort Descending").Delete
        .CommandBars("Cell").Controls("Sort Ascending").Delete
    End With
    On Error GoTo 0
    
End Sub
Private Sub AddOnRightClick()

    On Error Resume Next
    HeaderRow = 4 'How to pass the variable to the sub sorting the dat
    Dim SortAsceButton As CommandBarButton
    Dim SortDescButton As CommandBarButton
    With Application
        .CommandBars("Cell").Controls("Sort Descending").Delete
        Set SortDescButton = .CommandBars("Cell").Controls.Add(Temporary:=True, Before:=1)
    End With
    With Application
        .CommandBars("Cell").Controls("Sort Ascending").Delete
        Set SortAsceButton = .CommandBars("Cell").Controls.Add(Temporary:=True, Before:=1)
    End With
    With SortAsceButton
       .BeginGroup = True
       .Style = msoButtonIconAndCaption
       .Caption = "Sort Ascending"
       .FaceId = 125
       .OnAction = "ThisWorkbook.SortAscending"
    End With
    With SortDescButton
       .BeginGroup = True
       .Style = msoButtonIconAndCaption
       .Caption = "Sort Descending"
       .FaceId = 125
       .OnAction = "ThisWorkbook.SortDesc"
    End With
    Set SortAsceButton = Nothing
    Set SortDescButton = Nothing
    On Error GoTo 0
    
End Sub

Sub SortDesc()

    Select Case ActiveCell.Row
        Case HeaderRow
            If IsEmpty(ActiveCell.Value) Then Exit Sub
            Static MySortType As Integer
            MySortType = xlDescending
            'Target.CurrentRegion.Offset(1).Sort key1:=Target, order1:=MySortType, Header:=xlYes
            ActiveCell.CurrentRegion.Offset(0).Sort key1:=ActiveCell, order1:=MySortType, Header:=xlYes
            On Error Resume Next
            Err.Clear
    End Select
    
End Sub

Sub SortAscending()

    Select Case ActiveCell.Row
        Case HeaderRow
            If IsEmpty(ActiveCell.Value) Then Exit Sub
            Static MySortType As Integer
            MySortType = xlAscending
            'Target.CurrentRegion.Offset(1).Sort key1:=Target, order1:=MySortType, Header:=xlYes
            ActiveCell.CurrentRegion.Offset(0).Sort key1:=ActiveCell, order1:=MySortType, Header:=xlYes
            On Error Resume Next
            Err.Clear
    End Select
    
End Sub

[Rasm]

Perfect - exactly what the Doctor ordered - Thanks

Also use FaceId 3157 & 3158 - symbols for sorting

I uploaded the final XLSM files under tips-Tricks and downloads - this is a very usefull tool - Excel Fox & Admin - thanks for your help.

[fioramonti]

Hello Folks,
Maybe I am just having first time hickups to the site but where is data passed for the sort?

Or I am missing a point on this macro?

[Rasm]

Fioramonti

This routine sorts the entire sheet from the headerrow down -- are you looking to only sort a single column of data or a range of cells?

Rasm

[fioramonti]

Hello frasm,

Yes, I would be sorting a range of rows, single column.

Thanks

fioramonti