Thread: Code Crashes Excel 2010 but works in Excel 2003

Hello,

I have the following code that is crashing Excel 2010. It is applied to a range of cells that contain a column header. When the cell is double clicked it is suppose to sort by that column. Unfortunately, Excel crashes instead. This code works fine in 2003. Any suggestions would be appreciated.

Thanks!

Update: It's only the last sort criteria that crashes Excel. When removed, the macro runs fine and Excel does not crash but with it, Excel crashes. It looks like it runs as all the way through, sorts, and then crashes.

Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Row <> 12 Then Exit Sub
If ActiveCell = "Complete" Then
   Range("CoreWork").Sort Key1:=Cells(13, ActiveCell.Column), _
      Order1:=xlAscending, Key2:=Cells(13, ActiveCell.Column - 2), _
     Order2:=xlAscending, Key3:=Cells(13, 2), _
     Order2:=xlAscending, Header:=xlNo, _
      OrderCustom:=1, MatchCase:=False, _
      Orientation:=xlTopToBottom
      
ElseIf ActiveCell = "Business Day Actual" Then
   Range("CoreWork").Sort Key1:=Cells(13, ActiveCell.Column), _
      Order1:=xlDescending, Key2:=Cells(13, ActiveCell.Column - 1), _
     Order2:=xlAscending, Key3:=Cells(13, 2), _
     Order2:=xlAscending, Header:=xlNo, _
      OrderCustom:=1, MatchCase:=False, _
      Orientation:=xlTopToBottom
      
ElseIf ActiveCell = "Business Day Expected" Then
   Range("CoreWork").Sort Key1:=Cells(13, ActiveCell.Column), _
      Order1:=xlAscending, Key2:=Cells(13, 2), _
     Order2:=xlAscending, Header:=xlNo, _
      OrderCustom:=1, MatchCase:=False, _
      Orientation:=xlTopToBottom
      
ElseIf ActiveCell = "%" Then
   Range("CoreWork").Sort Key1:=Cells(13, ActiveCell.Column), _
      Order1:=xlAscending, Key2:=Cells(13, ActiveCell.Column - 4), _
     Order2:=xlAscending, Key3:=Cells(13, 2), _
     Order2:=xlAscending, Header:=xlNo, _
      OrderCustom:=1, MatchCase:=False, _
      Orientation:=xlTopToBottom
      
ElseIf ActiveCell = "Hedge Fund Manager" Then
   Range("CoreWork").Sort Key1:=Cells(13, ActiveCell.Column), _
      Order1:=xlAscending, Header:=xlNo, _
      OrderCustom:=1, MatchCase:=False, _
      Orientation:=xlTopToBottom

Else: Exit Sub
End If
End Sub

You haven't specified Order3 in any of the cases; you specify Order2 twice. Try this.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim iCol        As Long

    If Target.Row <> 12 Then Exit Sub

    With Range("CoreWork")
        iCol = ActiveCell.Column
        Select Case Target.Value
            Case "Complete"
                .Sort Key1:=Cells(12, iCol - 0), Order1:=xlAscending, _
                      Key2:=Cells(12, iCol - 2), Order2:=xlAscending, _
                      Key3:=Cells(12, 2), Order3:=xlAscending, _
                      Header:=xlYes, _
                      OrderCustom:=1, _
                      MatchCase:=False, _
                      Orientation:=xlTopToBottom

            Case "Business Day Actual"
                .Sort Key1:=Cells(12, iCol - 0), Order1:=xlDescending, _
                      Key2:=Cells(12, iCol - 1), Order2:=xlAscending, _
                      Key3:=Cells(12, 2), Order3:=xlAscending, _
                      Header:=xlYes, _
                      OrderCustom:=1, _
                      MatchCase:=False, _
                      Orientation:=xlTopToBottom

            Case "Business Day Expected"
                .Sort Key1:=Cells(12, iCol - 0), Order1:=xlAscending, _
                      Key2:=Cells(12, 2), Order2:=xlAscending, _
                      Header:=xlYes, _
                      OrderCustom:=1, _
                      MatchCase:=False, _
                      Orientation:=xlTopToBottom

            Case "%"
                .Sort Key1:=Cells(12, iCol - 0), Order1:=xlAscending, _
                      Key2:=Cells(12, iCol - 4), Order2:=xlAscending, _
                      Key3:=Cells(12, 2), Order3:=xlAscending, _
                      Header:=xlYes, _
                      OrderCustom:=1, _
                      MatchCase:=False, _
                      Orientation:=xlTopToBottom

            Case "Hedge Fund Manager"
                .Sort Key1:=Cells(12, iCol), Order1:=xlAscending, _
                      Header:=xlYes, OrderCustom:=1, _
                      MatchCase:=False, _
                      Orientation:=xlTopToBottom
        End Select
    End If
End Sub

probably this suffices:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  If Target.Row <> 12 Then Exit Sub

  If instr("|Complete|Business Day Actual|Business Day Expected|%|Hedge Fund Manager","|" & target & "|")>0 then Range("CoreWork").Sort Cells(13, target.Column),,Cells(13, 1),,,Cells(13, 2)
End Sub