Double click on table header to sort

[hajiali]

If Not Intersect(Target, Sheets("EMPLOYEE").Range("A1")) Is Nothing Then
    Cancel = True
    ActiveSheet.Unprotect Password:="testing"
    ActiveWorkbook.Worksheets("EMPLOYEE").ListObjects("EList").Sort.SortFields _
        .Clear
    ActiveWorkbook.Worksheets("EMPLOYEE").ListObjects("EList").Sort.SortFields _
        .Add Key:=Range("EList[[#All],[SEN'#]]"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("EMPLOYEE").ListObjects("EList").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
      ActiveSheet.Protect Password:="testing"
End If

the above code works perfect however would like to change line below to reference the table header instead of Range("A1"). this will be useful if I need to add a column In front of the column where I don't need to go change the range. the table Header is named "SEN#".

If Not Intersect(Target, Sheets("EMPLOYEE LIST").Range("A1")) Is Nothing Then

any help is greatly appreciated.

 

[Domenic]

Try the following...

If Not Intersect(Target, Range("EList[[#Headers],[SEN'#]]")) Is Nothing Then

However, your macro can be re-written as follows...

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

    If Not Intersect(Target, Range("EList[[#Headers],[SEN'#]]")) Is Nothing Then
        Cancel = True
        Me.Unprotect Password:="testing"
        With Me.ListObjects("EList").Sort
            With .SortFields
                .Clear
                .Add Key:=Range("EList[[#All],[SEN'#]]"), _
                    SortOn:=xlSortOnValues, _
                    Order:=xlAscending, _
                    DataOption:=xlSortNormal
            End With
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        Me.Protect Password:="testing"
    End If

End Sub

Hope this helps!

 

[hajiali]

Thanks for your help and tips. Works great.

 

[Domenic]

That's great, I'm glad I could help.

And thanks for your feedback.

Cheers!