CODEWITHSUNDEEP
excelvba
StumpedObject
StumpedObject

Reputation: 35

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

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)

Upvotes: 0

Views: 5800

Answers (1)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19782

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.

Upvotes: 4

Related Questions