Sorting Columns based on criteria

[SimondsJM]

Hello,

I am trying to find a way to add clickable sort functions to a sheet, kinda like you have in your typical E-mail.

By Date in Column B, By Alphabet in Column C, By Alphabet in Column D, by Number (Currency) in Column E.

Of course I would like the corresponding data in the row to follow the sort.

Right now I have buttons set as transparent (so you can see whats in the cell) to be used for the sorting function.

So on the sheet "Master List" i have 4 buttons, "CmdDate" will sort by date in Column B, "CmdVendor" will sort alphabetically by vendor name in column C, "CmdCourse" will sort alphabetically by course name in column D, and "CmdCost" will sort numerically (smaller to larger) in Currency format.

Any help is greatly appreciated.

 

[jim may]

I have used the below Sheet Code to accomplish this. Forget the creation of buttons, just RIGHT-CLICK on the Column Header you want to sort. Good Luck Jim

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    Cancel = True
    On Error Resume Next
    If Not Intersect(Target, Range("A1:F1")) Is Nothing Then
    SortCol = ActiveCell.Column
    With ActiveSheet.Range("A1").CurrentRegion
    .Sort Key1:=Target.Value, Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
     MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
     End With
    End If
    On Error GoTo 0
End Sub

 

[VoG]

Instead of buttons try this: right click the sheet tab, select View Code and paste in

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Static MySortType As Integer
Cancel = True
If MySortType = 0 Then
    MySortType = xlAscending
ElseIf MySortType = xlAscending Then
    MySortType = xlDescending
ElseIf MySortType = xlDescending Then
    MySortType = xlAscending
End If
Target.CurrentRegion.Sort key1:=Target, order1:=MySortType, Header:=xlYes
End Sub

Press ALT + Q to close the code window.

Now, double click in any column to sort by it. Double click the same column again to reverse the sort direction.

 

[jim may]

Range("A1:F1") ' <<<< Change to suit your Header row range

 

[SimondsJM]

VoG,
I like your setup to double click in the column, will this also work if the sheet is locked?

 

[VoG]

It will if you add the lines in red

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Static MySortType As Integer
Cancel = True
Me.Unprotect
If MySortType = 0 Then
    MySortType = xlAscending
ElseIf MySortType = xlAscending Then
    MySortType = xlDescending
ElseIf MySortType = xlDescending Then
    MySortType = xlAscending
End If
Target.CurrentRegion.Sort key1:=Target, order1:=MySortType, Header:=xlYes
Me.Protect
End Sub

 

[SimondsJM]

Is there a way to automatically enter the password to unprotect?

 

[VoG]

Sure, change abc to the password (twice)

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Static MySortType As Integer
Cancel = True
Me.Unprotect Password:="abc"
If MySortType = 0 Then
    MySortType = xlAscending
ElseIf MySortType = xlAscending Then
    MySortType = xlDescending
ElseIf MySortType = xlDescending Then
    MySortType = xlAscending
End If
Target.CurrentRegion.Sort key1:=Target, order1:=MySortType, Header:=xlYes
Me.Protect Password:="abc"
End Sub