Admin
I have not tried yours yet - but I will. However I remembered the Calendar control example posted on this site - where you can right_click and the get the choices added. So using that code idea - this allow me to now right_click the HeaderRow and then sort ascending or descending. In this example my HeaderRow=4
Here is my problem - how can I pass the HeaderRow value from my worksheet code down to the sub(s) in module1 - I have hardwired that HeaderRow=4 in the subs in module1 - but I want to remove that line.
Is there a way I can pass the code residing in sheet1 - to any new sheets that I add - The code is passed if I copy a sheet - rather than adding a sheet - using the Excel GUI - But I would like to actually be ab;e to copy the worksheet code to any sheet of my choice. In a perfect world the worksheet code should reside in the workbook.
I have attached the XLSM file
Again - thank you Site mangers - this site is great.
Worksheet code
Code:
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
Dim HeaderRow As Long
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 = "SortAscending"
End With
With SortDescButton
.BeginGroup = True
.Style = msoButtonIconAndCaption
.Caption = "Sort Descending"
.FaceId = 125
.OnAction = "SortDesc"
End With
Set SortAsceButton = Nothing
Set SortDescButton = Nothing
On Error GoTo 0
End Sub
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
AddOnRightClick
End Sub
Module1 code
Code:
Sub SortDesc()
HeaderRow = 4 'This variable should be passed from worksheet code
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()
HeaderRow = 4
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
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
Reply With Quote
Bookmarks