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

  • Thread starter Thread starter Ronio
  • Start date Start date
R

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
 
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
 
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
 
Back
Top