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