Creating a "Sort" hyperlink in code

[jerH]

I have built a utility that reads in a .csv file, does a bunch of calculations, and spits out a formatted report. Within the report there are multiple "sections". The user has now asked if I can make the column headings "clickable" in order to sort a section in either ascending or descending order (toggle back and forth) by just clicking on the column heading. Any pointers on how to achieve this? I can provide as much detail as necessary

 

[tonyyy]

jerH,

If your user is okay with a double click rather than a single click, you might consider the following...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim Rng As Range
Static sortOrdur As Long

Set Rng = Target.CurrentRegion
sortOrdur = IIf(sortOrdur = xlAscending, xlDescending, xlAscending)
Rng.Sort Key1:=Target, order1:=sortOrdur, Header:=xlYes
End Sub

The code should be pasted into the sheet module that corresponds to the report sheet. The code assumes the multiple "sections" can be defined as CurrentRegions (ie, separated by blank rows/columns), and that Headers exist in each section.

Cheers,

tonyyy

 

[jerH]

Double click would be fine....forgive my ignorance though, I've never used .CurrentRegion. The region(s) to be sorted will be dynamic in number and size, and they'll be adjacent to one another (columns in a report). What kind of ground work do I need to do to ensure the regions set up correctly?

THanks!

 

[tonyyy]

Range.CurrentRegion Property

"Returns a Range object that represents the current region. The current region is a range bounded by any combination of blank rows and blank columns. Read-only."

 

[Yongle]

@tonyyy - lovely simple code

But I think you forgot this line...

Cancel=True

and just a thought - if every section is a single column then to avoid inserting columns, perhaps...

Set Rng = Target.CurrentRegion.Resize(, 1)

 

[tonyyy]

Cancel = True

Thanks for that, Yongle...

 

[jerH]

This is very cool! I still have a couple of issues with implementing it though. This is probably where I pay for not including more detail in the original post.

1) Some of the rows in the sections to be sorted have merged fields....right now this throws an error. I can probably do away with the merged fields, but the aesthetics of the report would suffer. Do you know of any way around this?
2) CurrentRegion is looking for spaces to delimit things, so if there is a row of data directly above the column headers it treats those like the headers and includes the true headers in the sorted data. I can probably get around this by inserting a blank row and making it's height very small....
3) This is the kicker (given my limited abilities)...as is, the code for this even handler has to go on the worksheet itself. The way the tool works is the user specifies an input file, and then the code creates a new worksheet named for the date/location of the data file and puts the report on there. Is there any way to programmatically insert code in a worksheet? Or, more likely I suspect, is there a way to pass a worksheet name to an even handler-like function that is stored in a module?

Thanks for all your help!

 

[tonyyy]

1) Instead of Merging cells, use the Cells / Format Cells / Alignment / Horizontal function and choose "Center across selection"
2) Yes, CurrentRegion utilizes blank rows/columns to distinguish different regions/areas
3) Excel vba is extensible, meaning you can add code programmatically...

Sub AddEventProcedure()
'''' Add a reference to Microsoft Visual Basic for Applications Extensibility 5.3
Dim lastSheet As Worksheet, ws As Worksheet
Dim CodeMod As VBIDE.CodeModule
Dim Kode As String

With ThisWorkbook
    Set lastSheet = .Sheets(1)
    For Each ws In .Sheets
        If Val(Mid(ws.CodeName, 6)) > Val(Mid(lastSheet.CodeName, 6)) Then
            Set lastSheet = ws
        End If
    Next ws
End With

Set CodeMod = ActiveWorkbook.VBProject.VBComponents(lastSheet.CodeName).CodeModule

Kode = _
    "Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)" & vbNewLine & _
    "Dim Rng As Range" & vbNewLine & _
    "Static sortOrdur As Long" & vbNewLine & _
    "Set Rng = Target.CurrentRegion" & vbNewLine & _
    "sortOrdur = IIf(sortOrdur = xlAscending, xlDescending, xlAscending)" & vbNewLine & _
    "Rng.Sort Key1:=Target, order1:=sortOrdur, Header:=xlYes" & vbNewLine & _
    "Cancel = True" & vbNewLine & _
    "End Sub"

With CodeMod
    .InsertLines .countoflines + 1, Kode
End With
End Sub

Be sure to add a reference to Microsoft Visual Basic for Applications Extensibility 5.3.
It's assumed the last sheet added to the workbook is the sheet that needs the event handler code. It's also assumed you're not changing any of the sheet CodeNames.

 

[jerH]

Thanks so much for all the help! This works brilliantly.....

 

[tonyyy]

You're very welcome...