And by ‘right way’ I mean the way I want. JKP commented:
I wish MSFT would put a tablename box right into the “Format as Table” dialog as it is the first thing I do after formatting a range as a table. O, and always put that checkbox on. My tables always have a header row.
I couldn’t agree more. So why not repurpose Ctrl+T to do what I want.
Dim sh As Worksheet
Dim sName As String
Dim lo As ListObject, loExists As ListObject
Const sSHEETSTART As String = "Sheet"
Const sTABLESTART As String = "tbl"
Set sh = ActiveSheet
‘Get the name of the table from the user
sName = Application.InputBox("Enter the table name", "Table Name")
‘If the user didn’t click Cancel
If sName <> "False" Then
‘Start the table with ‘tbl’ if it doesn’t already
If Left$(sName, Len(sTABLESTART)) <> sTABLESTART Then
sName = sTABLESTART & sName
End If
‘Create the table and name it
Set lo = sh.ListObjects.Add(xlSrcRange, ActiveCell.CurrentRegion, , xlYes)
‘See if that name exists on this sheet
On Error Resume Next
Set loExists = sh.ListObjects(sName)
On Error GoTo 0
‘If the name doesn’t exist
If loExists Is Nothing Then
lo.Name = sName
‘If the sheet isn’t already specifically named, name it
If Left$(sh.Name, Len(sSHEETSTART)) = sSHEETSTART Then
On Error Resume Next
sh.Name = Replace$(lo.DisplayName, "tbl", vbNullString)
End If
End If
End If
End Sub
This code makes a lot of assumptions about how I work with tables, so it may not work for you. First, I ask for the table name. I start all my table names with tbl, so if I don’t include that the code includes it for me. Next, I create a new ListObject based on the CurrentRegion of the ActiveCell. This is different than what Excel does. If you only have one cell selected, Excel will use the CurrentRegion. If you have more than one cell selected, Excel assumes you’ve defined the range you want and uses that. I put one table on one sheet and it’s the only thing on there. Therefore, I always want everything on that sheet to be the table.
Next, I see if a table with that name already exists on the sheet. If it does, skip the whole naming part.
Finally, I change the name of the sheet if it’s still named the generic ‘Sheetx’. I drop the ‘tbl’ part from the DisplayName property and name the sheet. The error avoidance is in case there’s already a sheet with that name. In that case, the name remains unchanged.
Why the DisplayName? If you name a table tblList, you can’t name another table tblList on the same sheet. In fact, in the user interface you can’t name another table tblList in the whole workbook. But in code, you can name another table tblList as long as it’s on a different sheet. If that name already exists, ListObject.Name remains tblList, but ListObject.DisplayName is changed to tblList_1. That’s why I check for the existence of that table on the same sheet but not the whole workbook. And that’s why I use the DisplayName to name the sheet.
I should have skipped all this error checking and just put a big On Error Resume Next at the top. I probably will never have two tables with the same name, and if I accidentally did, it would just keep the default name.