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
Code: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




Reply With Quote



Bookmarks