Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

I can see from the .NET API (VSTO) for ListObject that events do exist for this object. VSTO Docs

However I am not coding in .NET, I am just doing vanilla Excel VBA and would like to tap into the events that the ListObject raises.

The official Microsoft Documentation on ListOjbect does not show any events but I am hoping that perhaps there is an "unofficial" way this can be achieved?

There are no direct Events but there are workarounds.

eg. you can check whether user tried to click inside or one row under the ListObject

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   Dim tbl As ListObject: Set tbl = ListObjects("Table1")
   If Not Intersect(Target, tbl.Range.Offset(1, 0)) Then
        Exit Sub 'clicked elsewhere, exit
        'tried to access table do something <code here>
   End If
End Sub
I made a package (alpha W.I.P, I may change the API):
https://github.com/Greedquest/ListObject-WithEvents
From the README docs, stick this in the sheet code-behind which has your table (there are other ways to hook the table up, but only worksheets workbook and class modules can declare WithEvents)
Private WithEvents fooTableEvents As TableWatcher
Sub StartListening() 'press F5 to start listening. You could call this from Workbook_Open
   Set fooTableEvents = TableWatcher.Create(Me.ListObjects("foo"))
End Sub
Private Sub fooTableEvents_RowAppended(ByVal where As ListRow)
  Debug.Print "New Row added to table Foo -"; where.DataBodyRange.Address
End Sub
Current list of events exposed:
Public Event RowInserted(ByVal where As ListRow)
Public Event RowDeleted(ByVal prevLocation As Range)
Public Event RowAppended(ByVal where As ListRow)
Public Event ColumnInserted(ByVal where As ListColumn)
Public Event ColumnDeleted(ByVal prevLocation As Range)
Public Event ColumnAppended(ByVal where As ListColumn)
Public Event ColumnNameChanged(ByVal which As Range)
Public Event DataValueChanged(ByVal where As Range)
source
I don't know about any specific events for Listobjects but you can reproduce that behaviour easily using the events of the sheets.
If you want an event to fire when you just CLICK in ONE cell:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim mytbl As ListObject
Set tbl = thisworkbook.sheets("whatever sheet").ListObjects("Table1")
dim overlap as range
set overlap = Intersect(Target, mytbl.databodyrange)
If Not overlap in nothing Then
   'your selection is totally or partially inside the table
    if overlap.count=1
    ' you selected only one cell
    ' do something
    ' If you want to access the cell selected
    ' use target.range
    msg box('you did not make a proper selection of one cell inside the listobject')
End If
End if
End Sub
If you want an event to fire when you CHANGE THE VALUE OF A CELL OF THE LISTOBJECT:
You can only change a cell at a time. So no necessary to check for amount of cells. Its always one.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim mytbl As ListObject
Set tbl = thisworkbook.sheets("whateversheet").ListObjects("Table1")
' the list object table is in sheet 'whateversheet"
dim overlap as range
set overlap = Intersect(Target, mytbl.databodyrange)
If Not overlap is nothing Then
    ' your selection is inside the table
    ' code is here when you change the value of a cell of the table.
    ' do some stuff
    ' if you want to add the introduced value: 
    newvalue=target.value
    ' You might inform the user that the change took place outside the listobject
End If
End Sub
There are other events you can program. double click, etc etc.
basically it is always about finding if the event is fired by a cell of the sheet that belongs to the list object (hence the intersec of the ranges). And if it is then fire the corresponding code.
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.