Double Click Table Header to Sort

[Jeff1959]

Hi all, I have this code that I have used for a long time to sort by double clinking on a header. The problem is it does not work when I try to use it on a Excel formatted table. I'm new to VBA so figuring this one out has got me stumped. The error I keep getting is "Methoud Range of Object Worksheet Failed" Any help will be greatly appreciated. Heres the current code that Im using.



Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim LRow As Long '-- D.McRitchie, 2006-04-01 double-click on column to sort
'Find last row in Column A with content
LRow = Cells(Rows.Count, 1).End(xlUp).Offset(0, 0).row
Rows("2:" & LRow).Sort Key1:=Cells(2, ActiveCell.Column), _
Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End Sub


Thanks

[Bob Phillips]

Are you referring to Excel 2007?

[Jeff1959]

Yes, sorry, I should have included that information. Thanks

[mdmackillop]

Works fine for me. Can you post a sample workbook with your data layout?

[Bob Phillips]

Yeah, I see what you mean.

This seems to work



Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim LRow As Long '-- D.McRitchie, 2006-04-01 double-click on column to sort

'Find last row in Column A with content
LRow = Cells(Rows.Count, 1).End(xlUp).Offset(0, 0).Row

With Me.ListObjects("Table1")

.Sort.SortFields.Clear
.Sort.SortFields.Add _
Key:=Me.Cells(3, Target.Column).Resize(LRow - 2), _
SortOn:=xlSortOnValues, _
Order:=xlAscending, _
DataOption:=xlSortNormal
With .Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With

End Sub

[Jeff1959]

Thanks that worked great, now I understand where I was going wrong.:beerchug:

[Bob Phillips]

I think that it is that a formatted table becomes an object in 2007, and so you have to work with the object. I must admit to the key value though, I would have thougt you would use the table column id.