Using VBA to return the Table Name corresponding to a given ActiveCell Selection

Question

For a given ActiveCell in Excel how can VBA return the Table Name that appears in the properties grouping that becomes visible when the Table Tools Design tab is activated. So for example in the immediate window:

?ActiveSheet.ListObjects(2)

 Table3600

but if I select a cell (potentially within a table) I can't seem to find a property that will either return the selected cell's containing table's name. I am trying to either store the return value or to state that the current cell is not in a table.

TIA (This seems very fundamental yet I am nowhere)

I am not sure to understand what you want: return the table's name, return the cell's value or find out if the cell is in the table. Could you develop? — Rémi
– Rémi, Commented Jul 6, 2016 at 11:05
Thank you for your interest Remi, what I am trying to do is stack tables created from a pivot-table "double-click" and integrate them into a single table, by identifying the tables identity I can remove its table status and then integrate it in to the growing upper table stack — StumpedObject
– StumpedObject, Commented Jul 6, 2016 at 18:54

Darren Bartrup-Cook · Accepted Answer · 2016-07-06 11:35:29Z

4

These return the name of the table of the selected cell. If there is no table then a run-time error is thrown.

Selection.ListObject.Name
Selection.ListObject.DisplayName

You can avoid the error using:

If Not Selection.ListObject Is Nothing Then
    MsgBox Selection.ListObject.Name
End If

?ActiveSheet.ListObjects(2) will give the name of the second table on a sheet and throw an error if there is no second table.

answered Jul 6, 2016 at 11:35

Darren Bartrup-Cook

20.2k2 gold badges27 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

StumpedObject Over a year ago

Thanks Darren your method works well, I am fairly new to the list object in Excel but not in Access so I am often a bit startled with its use. I tried both in the immediate window (.Name vs .DisplayName) and they both worked. Moreover I also appreciate you explaining the indexing function pattern for ?ActiveSheet.ListObjects(2) and have tried it just now with 1, 2, and 3 on one of my table stacks: this is very powerful given that it does not depend on the specific coordinate of the active cell, thank you again.

Collectives™ on Stack Overflow

Using VBA to return the Table Name corresponding to a given ActiveCell Selection

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related