Results 1 to 4 of 4

Thread: Sort data sheet by right_click of mouse

  1. #1
    Senior Member
    Join Date
    Apr 2011
    Posts
    190
    Rep Power
    14

    Sort data sheet by right_click of mouse

    This code example was developed together with Excel Fox & the Admin of this site - thank you very much.

    It allows you to sort data for the entire sheet (all columns) - starting (below) at a HeaderRow. So the entire sheet is sorted similar to how Windows explorer allow you to sort files by click on the ColumnHeader.

    All you have to do is set the HeaderRow variable. In the attached XLSM file the HeaderRow =4

    Simply right_click on the on a cell in the HeaderRow.

    I thought this code could be of general interest.

    Good luck coding -- Rasm


    Workbook code
    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 'You add code to set the header row
        
        
        
        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 = 3157
           .OnAction = "ThisWorkbook.SortAscending"
        End With
        With SortDescButton
           .BeginGroup = True
           .Style = msoButtonIconAndCaption
           .Caption = "Sort Descending"
           .FaceId = 3158
           .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
                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
                ActiveCell.CurrentRegion.Offset(0).Sort key1:=ActiveCell, order1:=MySortType, Header:=xlYes
                On Error Resume Next
                Err.Clear
        End Select
    End Sub
    Attached Files Attached Files
    xl2007 - Windows 7
    xl hates the 255 number

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Thanks Rasm.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Senior Member
    Join Date
    Apr 2011
    Posts
    190
    Rep Power
    14
    This modification will result in the right_click options for sorting is only shown when the HeaderRow has focus

    Code:
    Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
        If Target.Row = HeaderRow Then
                AddOnRightClick
            Else
                DeleteOnRightClick
        End If
    End Sub
    xl2007 - Windows 7
    xl hates the 255 number

  4. #4
    Junior Member
    Join Date
    Dec 2012
    Posts
    3
    Rep Power
    0
    Really good

Similar Threads

  1. Sort Data Using Formula To Find Top X
    By mahmoud-lee in forum Excel Help
    Replies: 12
    Last Post: 06-02-2013, 10:13 PM
  2. Replies: 6
    Last Post: 05-10-2013, 01:13 AM
  3. Replies: 2
    Last Post: 12-26-2012, 08:31 AM
  4. Excel Macro to Sort Data if a value changes in defined range
    By Rajesh Kr Joshi in forum Excel Help
    Replies: 4
    Last Post: 09-05-2012, 10:31 AM
  5. Sort Data When a Header Is Clicked
    By Rasm in forum Excel Help
    Replies: 9
    Last Post: 08-01-2012, 06:46 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •