[SOLVED] How can I add an interaction to rows without assigning a button to each?

How can I add an interaction to rows without assigning a button to each?

This is some what of an opinion quest, but it is regarding excel vba events

I have a table that contains the history of our sales, but I want the user to be able to interact with each row in some way, so that it opens that sale in the editor.

As someone who comes from Javascript, my first thoughts were click or double click events on the row cells, but in excel these events already have their functions, so its not very intuitive

What is an "excel-like" way that I can achieve this? Or, what are my options?

Anyone that tried this before might have a successful method to share?

Or is it okay to add a button to each row? There are 2.000 + rows and growing

Solution

Sample code that uses the right-click context menu and adds links to two subroutines (GetFields and GetTables):

Code in ThisWorkbook code module:

Private Sub Workbook_Activate()
    Call AddToCellMenu
End Sub

Private Sub Workbook_Deactivate()
    Call DeleteFromCellMenu
End Sub

Code in a standard code module:

Option Explicit

Sub AddToCellMenu()
    Dim ContextMenu As CommandBar
    Dim MySubMenu As CommandBarControl

    ' Delete the controls first to avoid duplicates.
    Call DeleteFromCellMenu

    ' Set ContextMenu to the Cell context menu.
    Set ContextMenu = Application.CommandBars("Cell")

    With ContextMenu.Controls.Add(Type:=msoControlButton, before:=1)
        .OnAction = "'" & ThisWorkbook.Name & "'!" & "GetFields"
        .FaceId = 498
        .Caption = "Get Field Names"
        .Tag = "My_Cell_Control_Tag"
    End With
    With ContextMenu.Controls.Add(Type:=msoControlButton, before:=1)
        .OnAction = "'" & ThisWorkbook.Name & "'!" & "GetTables"
        .FaceId = 585
        .Caption = "Get Table Names"
        .Tag = "My_Cell_Control_Tag"
    End With

    ' Add a separator to the Cell context menu.
    ContextMenu.Controls(3).BeginGroup = True
End Sub

Sub DeleteFromCellMenu()
    Dim ContextMenu As CommandBar
    Dim ctrl As CommandBarControl

    ' Set ContextMenu to the Cell context menu.
    Set ContextMenu = Application.CommandBars("Cell")

    ' Delete the custom controls with the Tag : My_Cell_Control_Tag.
    For Each ctrl In ContextMenu.Controls
        If ctrl.Tag = "My_Cell_Control_Tag" Then
            ctrl.Delete
        End If
    Next ctrl

    ' Delete the custom built-in Save button.
    On Error Resume Next
    ContextMenu.FindControl(ID:=3).Delete
    On Error GoTo 0
End Sub

See also: Adding Controls to the Cell Context Menu by Using VBA Code (which is probably where I originally got the code from)

Note: The FaceId property is fun to play with. I forget now where I obtained the list of what icon each value referred to. (It might have been from one of the links on the MSDN page.) Edit: I doubt if this is where I originally saw it, but this Stack Overflow answer by Horst Schmid is probably useful.