Excel using auto-generated hyperlinks to hide rows in a table-CodePudding

I have a table where I want to be able to hide individual rows at a mouse click. The (seemingly) easiest solution I've found is to have a column filled with hyperlinks that call a macro to hide the row that they're in.

There are two ways of calling macros from hyperlinks: using Worksheet_FollowHyperlink with manual hyperlinks, and using =HYPERLINK.

The former works fine, except there's no way (that I've found) to have them generate automatically when new rows are added to the table. I would have to either manually copy them down every time, which is unviable, or add them with VBA, which adds a bunch of complexity to an otherwise simple task.

The latter generates fine, being a formula, but it doesn't actually work. It doesn't trigger Worksheet_FollowHyperlink, and when using =HYPERLINK("#MyFunction()") it just doesn't hide rows (or do much other than editing cells contents).

Function MyFunction()
    Set MyFunction = Selection
    Selection.EntireRow.Hidden = True
End Function

Is there a good solution to this?

CodePudding user response：

Rather than a Hyperlink, you could handle a Double Click event on the table column

Something like

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim NameOfTableColumn As String
    
    On Error GoTo EH:
    NameOfTableColumn = "DblClickToHide" ' update to suit your table
    
    If Not Application.Intersect(Target, Target.ListObject.ListColumns(NameOfTableColumn).DataBodyRange) Is Nothing Then
        Target.EntireRow.Hidden = True
        Cancel = True
    End If
Exit Sub
EH:
    
End Sub

CodePudding user response：

Please, copy the next code in the sheet code module where the table to be clicked exists. Clicking on each cell in its first column (dynamic to rows adding/insertions/deletions), the clicked row will be hidden:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   Dim tbl As ListObject
   If Target.cells.count > 1 Then Exit Sub
   Set tbl = Me.ListObjects(1) 'you may use here the table name
   If Not Intersect(Target, tbl.DataBodyRange.Columns(1)) Is Nothing Then
        Application.EnableEvents = False
          Target.EntireRow.Hidden = True
        Application.EnableEvents = True
   End If
End Sub

It would be good to think about a way to unhide the hidden row if/when necessary. If only a row should be hidden at a time, it is easy to unhide all the rest of column cells...