PDA

View Full Version : Solved: Sort and search on a table



aloy78
09-21-2011, 12:51 AM
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

Bob Phillips
09-21-2011, 02:12 AM
This will handle the sort for you



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

aloy78
09-22-2011, 08:02 PM
This will handle the sort for you



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

Wow, Neat Man. :beerchug: