This post may contain affiliate links. Please read my disclosure for more info.

Name an Excel Table when created

Name Table when created

I love the Tables feature of Excel, it just makes everything else in the spreadsheet easier to work with. However, one of the biggest frustrations is that there is no option to name the Table at the time of creation.  By default, we are given a unique name like Table1 if it is the first table created, or Table2 if it is the second table created etc, etc.  I see so many spreadsheets with these default names still used.  It is such a shame to see formulas like this:

=SUM(Table1[Total])

The user went to the trouble of creating a Table, but didn’t quite finish the task.  In the formula above, can see it is the SUM of a column called Total, but total of what?  I have no idea.  If the Table had a meaningful name it would be much more useful.

=SUM(outstandingDebts[Total])

Just from looking at that formula, I know it is the Total of the outstanding debts. Simple.

It is easy enough to rename a Table.  Click on the Table, then change the Table Name from the Table Tools: Design ribbon.

Rename a Table

But wouldn’t it be better if there were a field to enter the name in the Create Table window.  Look at the screenshot below, we can see options to select the range and use a header, but no option to name the Table.

Create Table window

There are a few VBA options to solve this problem.  Some involve full Add-ins, such Jan Karel Pieterse’ Table Tools, others include custom userForms.  I’ve gone for a simple little solution, which sits nicely in my Personal Macrobook.

Name a Table at creation with VBA

Copy the following code into a standard module within your Personal Macrobook.

Sub CreateNameTable()

'Create variables
Dim tableName As String
Dim dialogOutput As Boolean

'Show the InputBox to capture the Table Name
tableName = InputBox("Enter a valid Table name:", "Create Table")

'If cancel clicked on InputBox then Exit the macro
If tableName = vbNullString Then Exit Sub

'Capture Error if unable to create Table in that location
On Error Resume Next

'Show the Create Table dialog box
dialogOutput = Application.Dialogs(xlDialogCreateList).Show

'Exit the macro If error from creating Table
If Err.Number <> 0 Then

    MsgBox "Unable to create a Table in that location.", vbOK, _
        "Invalid Table location"
    Exit Sub

End If

'Turn error checking back on
On Error GoTo 0

'If Table not created then Exit the macro
If dialogOutput = False Then Exit Sub

'Catch an error if Table name is invalid
On Error Resume Next

'Apply new Table name to the created Table
ActiveSheet.ListObjects(ActiveSheet.ListObjects.Count).Name = tableName

'If Table name invalid display error message
If Err.Number <> 0 Then

    MsgBox "Table name '" & tableName & "' is invalid." & _
    vbNewLine & vbNewLine & "The default name '" & _
        ActiveSheet.ListObjects(ActiveSheet.ListObjects.Count).Name & _
        "' has been used.", vbOKOnly, "Invalid Table name"

End If

'Turn error checking back on
On Error GoTo 0

End Sub

When you runthis macro, it will open a standard VBA input box to collect the name of the Table.

Enter Table Name

If the user clicks OK on the input box, the normal Create Table window then will then open.

The macro will initially create the table as normal, allowing Excel to choose the default name.  The VBA code will then rename the last Table created, changing the default name to the useful name provided in the input box at the start.

It may not be the most elegant solution, but it works 🙂

Hijack the Shortcut Keys

Running the code form the Developer Tab would probably take more time than it saves.  But if we can hijack a shortcut key, we can run it very quickly.  Place the following code in the Workbook module of your Personal Macrobook.

Private Sub Workbook_Open()

Application.OnKey "^{t}", "CreateNameTable"

End Sub

This will hijack the normal Ctrl + T shortcut to create a Table.

The shortcut is created each time the Personal Macrobook is opened, which if you’ve got one, is every time you open a session of Excel.

“What about the buttons on the ribbon, can we hijack those too?”  Yes, you could do that also.  But I’m not going to cover it in this post, as it’s a lot of technical detail which will overshadow the purpose we’re trying to achieve with this post.

All that’s left now is to save your Personal Macrobook and enjoy the benefit of naming Tables when they are created.

Leave a Reply

Your email address will not be published. Required fields are marked *