How to find table name?

[Maciej]

Hello,
I am looking for a way to find out name of a table that active cell belongs to.
I need to write simple table sort macro. The issue is that the sheet will be copied many times, which will change name of a table that is in the sheet. And the table name is needed as parameter for sorting. Is there a way to select active cell in the table area and find out what is the name of the table? Or is it any other way to find out name of a table in a sheet?
Kind regards
Maciej

 

[NARAYANK991]

Hi ,

Something like this ?

Public Sub FindTableName()
                Dim TN As Variant
               
                On Error Resume Next
                TN = ActiveCell.ListObject.Name
                If IsEmpty(TN) Then
                  MsgBox "Active cell is not part of a table"
                Else
                  MsgBox "Activecell is part of a table named " & ActiveCell.ListObject.Name
                End If
                On Error GoTo 0
               
End Sub

Narayan

 

[Maciej]

Thanks!
Anyway, TN needs to be String, not Variant.

 

[shrivallabha]

Not necessary (i.e. it is not wrong to use Variant). Variant can store string results also. If you want to use string then you need to modify the code little more.

Public Sub FindTableName()
Dim TN As String

On Error Resume Next
TN = ActiveCell.ListObject.Name
If TN = "" Then
    MsgBox "Active cell is not part of a table"
Else
    MsgBox "Activecell is part of a table named " & ActiveCell.ListObject.Name
End If
On Error GoTo 0
   
End Sub

It just goes to show that there are many ways to get the same result.

 

[Maciej]

Yes, you are right. I used string as table sorting function requires the table name.
Anyway, the crucial line is this one: TN = ActiveCell.ListObject.Name
I used it in my macro and it works well. And I know that there is a table in the sheet, so I do not need to check, if it is present.
Thanks a lot!