Tables - Change Name from original given by Excel

[sswcharlie]

Hi
It is not possible to give a new table a user selected name, but can be changed only afterwards.

I would like to use vba to look at selected table, and change name to a specific cell value (name), say Tbl_sales (in say B14) That is once the table is selected then run the macro.


Thanks
Charles Harris

 

[Debaser]

You could use something like this:

Sub renameTable()
    Dim lo As ListObject
    On Error Resume Next
    Set lo = ActiveCell.ListObject
    If lo Is Nothing Then
        MsgBox "Please select a cell in a Table first!"
        Exit Sub
    End If
    lo.Name = Range("B14").Value
    If Err.Number <> 0 Then
        MsgBox "Cannot rename selected Table to: " & Range("B14").Value
    End If
End Sub