February 8, 2020
I have the following VBA that works perfect:
Private Sub Worksheet_BeforeDoubleClick( _
ByVal Target As Range, Cancel As Boolean)
Dim KeyRange As Range
Dim ColumnCount As Integer
ColumnCount = Range("ADP").Columns.count
Cancel = False
If Target.Row = 1 And Target.Column <= ColumnCount Then
Cancel = True
Set KeyRange = Range(Target.Address)
If Target.Value = "Rank" Or Target.Value = "ADP" Then
SortOrder = xlAscending
Else
SortOrder = xlDescending
End If
Range("ADP").Sort Key1:=KeyRange, Order1:=SortOrder, Header:=xlYes
End If
End Sub
What I would like to add to this is another "If Target.Value" equals column "Data," I want the double-click to sort based upon multiple columns, Data and Data2, both ascending. I have not been able to figure out how to properly insert with above. Thanks.
Trusted Members
October 17, 2018
Since you have this macro and it works I suggest you do the following.
Start the macro recorder.
1. select the table or are
2. set Sort from the menu
3. select the columns and specify the sort order for each column
4. Click OK
5. Stop the macro recorder
Then open VBA editor and you can see how it's buitl up you can daapt it to your own needs
If you can't figure it ut after that I suggest you post a sample file with non-private data AND the macro's you have
February 8, 2020
I was just coming to upload a file, realizing I forgot.
See attached. Sheet1 is the VBA working fine other than when I double-click "Data", I want it to sort the table based upon both "Data" (ascending) and the 2nd level "Data2" (ascending).
Sheet2 shows the outcome I would like when double-clicking "Data."
Trusted Members
October 17, 2018
Try this: Paste this code in the worksheet replacing your own code
Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim tbl As ListObject
Set tbl = ActiveSheet.ListObjects("ADP")
Dim SortOrderField As Integer
Cancel = False
If Not Intersect(Target, tbl.HeaderRowRange) Is Nothing Then
If Target.Value = "Rank" Or Target.Value = "ADP" Then
SortOrderField = 1
Else
SortOrderField = 2
End If
tbl.Sort.SortFields.Clear
tbl.Sort.SortFields.Add2 Key:=Range(tbl.Name & "[" & Target.Text & "]"), SortOn:=xlSortOnValues, Order:=SortOrderField, DataOption:=xlSortNormal
With tbl.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Target.Offset(1, 0).Activate
Else
Cancel = True
End If
End Sub
February 8, 2020
I appreciate the assistance but the above code does not achieve the objective. When double-clicking the "Data" column, it is sorting the "Data" column descending and the "Data1" column is not in any corresponding order. The goal is, when double-clicking "Rank" or "ADP", sort the table ascending (this works). When selecting "Data," sort the table with "Data" ascending and within that sort, "Data1" ascending (this does not seem to work). All other columns when a double-click occurs, sort Descending (this works).
I have re-uploaded the file with your code above and displaying the desired outcome in Sheet2. Thanks.
Trusted Members
October 17, 2018
1 Guest(s)