Adding another column to automatically sort

[TJP222]

Howdy,

I have this code here that automatically sorts my "Priority" column when a value is changed. I would like it to sort the priority column first and then do a secondary sort by "Due Date." I have fiddled around with it quite a bit and I can't seem to get it to work. Any help is appreciated. Thanks.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim SalesTable As ListObject
Dim SortCol As Range
Set SalesTable = ActiveSheet.ListObjects("ToDo_List")
Set SortCol = Range("ToDo_List[Priority]")
If Not Intersect(Target, SortCol) Is Nothing Then
With SalesTable.sort
.SortFields.Clear
.SortFields.Add Key:=SortCol, Order:=xlAscending, CustomOrder:="High,Medium,Low"
.Header = xlYes
.Apply
End With
End If
End Sub

 

[Micron]

Add another .SortFields.Add line and specify the column number or another range variable as the key. AFAIK, the first sort should be last in the code, the last sort should be first.
If you post code, please use code tags (vba icon on posting toolbar) to maintain indentation and readability.

 

[TJP222]

Add another .SortFields.Add line and specify the column number or another range variable as the key. AFAIK, the first sort should be last in the code, the last sort should be first.
If you post code, please use code tags (vba icon on posting toolbar) to maintain indentation and readability.

I have tried that but I must be doing something wrong. I get "Compile Error: Type Mismatch" and it highlights "C1". Here's the code with indentions.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim SalesTable As ListObject
Dim SortCol As Range
Set SalesTable = ActiveSheet.ListObjects("ToDo_List")
Set SortCol = Range("ToDo_List[Priority]")
If Not Intersect(Target, SortCol) Is Nothing Then
With SalesTable.sort
.SortFields.Clear
.SortFields.Add Key:="C1", Order:=xlAscending
.SortFields.Add Key:=SortCol, Order:=xlAscending, CustomOrder:="High,Medium,Low"
.Header = xlYes
.Apply
End With
End If
End Sub

 

[TJP222]

I have tried that but I must be doing something wrong. I get "Compile Error: Type Mismatch" and it highlights "C1". Here's the code with indentions.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim SalesTable As ListObject
Dim SortCol As Range
Set SalesTable = ActiveSheet.ListObjects("ToDo_List")
Set SortCol = Range("ToDo_List[Priority]")
If Not Intersect(Target, SortCol) Is Nothing Then
With SalesTable.sort
.SortFields.Clear
.SortFields.Add Key:="C1", Order:=xlAscending
.SortFields.Add Key:=SortCol, Order:=xlAscending, CustomOrder:="High,Medium,Low"
.Header = xlYes
.Apply
End With
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim SalesTable As ListObject
Dim SortCol As Range
Set SalesTable = ActiveSheet.ListObjects("ToDo_List")
Set SortCol = Range("ToDo_List[Priority]")
If Not Intersect(Target, SortCol) Is Nothing Then
    With SalesTable.sort
        .SortFields.Clear
        .SortFields.Add Key:="C1", Order:=xlAscending
        .SortFields.Add Key:=SortCol, Order:=xlAscending, CustomOrder:="High,Medium,Low"
        .Header = xlYes
        .Apply
    End With
End If
End Sub

 

[Micron]

It doesn't know what C1 is. It's expecting a range and C1 is just text. Did you try
Key:=Range("C1")? You might have to qualify that with ActiveSheet. If so, that should be safe enough as the active sheet should always be the one the code is running against since you're using Worksheet_Change (as long as you don't activate a different sheet during the event).

 

[TJP222]

It doesn't know what C1 is. It's expecting a range and C1 is just text. Did you try
Key:=Range("C1")? You might have to qualify that with ActiveSheet. If so, that should be safe enough as the active sheet should always be the one the code is running against since you're using Worksheet_Change (as long as you don't activate a different sheet during the event).

Thanks Micron

 

[Micron]

Glad I could help.