How do I: vba to create column sorting buttons/hyperlink

[Ronio]

I would like to create a VBA that will take my column headings and create
either a hyperlink or a button for each column heading.
When the link/button is clicked it will sort the table by that column.

Thx,
Ronio

 

[A possible solution]

Inserting this into the ThisWorkbook section might get you close to what you
are looking for (although it does require double-clicking on the header
cells):

Dim rTable As Range

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target
As Range, Cancel As Boolean)
Set rTable = TableRange()
If Target.Row = headerRow Then Call SortByColHeader(Target)
End Sub

Function TableRange() As Range
Dim topLeft As Range
Set topLeft = Range("A1")
topLeft.Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Set TableRange = Selection
End Function

Sub SortByColHeader(r As Range)
rTable.Select
Selection.Sort Key1:=r, Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
'rTable.Cells(1, 1).Select
End Sub

Hope this helped,
Rolf

 

[Patrick Molloy]

using the sheet's on double click event makes this trivial:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
'sorts by the selected column, assumes row 1 is a header row
If Target.Row = 1 Then
Target.CurrentRegion.Sort Target, xlAscending, header:=xlYes
End If

End Sub

 

[Ronio]

Thank-you Rolf,

this worked perfectly.