Solved: Sort and search on a table

Hi,

I have here my guitar playlist that I would like to add some vba codes in it :cloud9:

I would like to be able to:

1. sort the table header either by ascending or descending by clicking on the table header.
2. I want to search for the artist / song title thru my constantly growing records :thumb
3. A reset button to clear my search.

I've attached a sample file

This will handle the sort for you

[vba]

rivate Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Static SortOrder(1 To 14) As Long
Dim ColNo As Long

If Not Intersect(Target, Me.Range("A7:N7")) Is Nothing Then

With Me.ListObjects("Table1")

ColNo = ActiveCell.Column
If SortOrder(ColNo) = xlAscending Then

SortOrder(ColNo) = xlDescending
Else

SortOrder(ColNo) = xlAscending
End If

.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=Range("Table1[" & .ListColumns(ColNo) & "]"), _
SortOn:=xlSortOnValues, _
Order:=SortOrder(ColNo), _
DataOption:=xlSortNormal
With .Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With

Target.Offset(1, 0).Select
End If
End Sub[/vba]

Quote:

---

Originally Posted by xld

This will handle the sort for you

[vba]

rivate Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Static SortOrder(1 To 14) As Long
Dim ColNo As Long

If Not Intersect(Target, Me.Range("A7:N7")) Is Nothing Then

With Me.ListObjects("Table1")

ColNo = ActiveCell.Column
If SortOrder(ColNo) = xlAscending Then

SortOrder(ColNo) = xlDescending
Else

SortOrder(ColNo) = xlAscending
End If

.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=Range("Table1[" & .ListColumns(ColNo) & "]"), _
SortOn:=xlSortOnValues, _
Order:=SortOrder(ColNo), _
DataOption:=xlSortNormal
With .Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With

Target.Offset(1, 0).Select
End If
End Sub[/vba]

---

Wow, Neat Man. :beerchug: