What Characters are Legal in a Worksheet Name

February 16, 2018 - by Bill Jelen

Worksheet names can be changed to almost anything. It is the *almost* part that is fuzzy for me. There are a few characters that aren't allowed in a worksheet tab. But how do you figure out which characters they are? Today, I will show you how.

One of my most popular videos on YouTube is Podcast #1505 - Next Invoice Number. I can remember downloading an invoice template from Microsoft 20 years ago and being disappointed that there was nothing in the template to increment the invoice number after each Save. Six years ago, I recorded a short video with a few lines of VBA code to update the invoice number at each save. 222,000 views later, it remains my #1 video.

Three times a week, I am contacted by someone who wants to change the macro to do something different. Last week, someone tells me that my code is giving them a 1004 error. It is always tough to troubleshoot without seeing the workbook. The line of code that was throwing the error was a line that renamed the worksheet to the value stored in cell E5.

Here is how I remind myself which characters are illegal. Follow these steps:

1. Press Ctrl + N for a new Excel workbook
2. Formula in A1 is =ROW(). Copy down to A1:A255. This quickly gives you the numbers 1 to 255
3. Formula in B1 is =CHAR(A1). Copy down to B1:B255. This gives you the 255 ASCII characters. You will notice a capital A in row 65.
4. ---

   ---

   Press Alt + F11 to open VBA. From the VBA menu, choose Insert Module. Copy the following code into VBA.

   Sub CheckAll()
       On Error Resume Next
       For i = 1 To 255
           Err.Clear
           ActiveSheet.Name = "A" & Cells(i, 2).Value
           Cells(i, 3).Value = Err.Number
       Next i
   End Sub

5. Click anywhere inside the macro. Press F5 to Run. It will only take a few seconds. Press Alt + Q to close and return to Excel. The macro has attempted to name the worksheet with 255 different names. Column C is showing the error code after each attempt. Zero is good. 1004 is bad.
6. It would be nice to add some headings so you can filter. Copy A1:C255. Paste as Values. Insert a new Row 1 with headings of Code, Character, Name
7. Turn on the Filter. Open the dropdown in C1. Uncheck anything with an error of 0. The remaining 9 rows show an error of 1004.

As you can see in the image below, the 9 characters '*/:?[\] are the ones that can not be used in a worksheet name. (Those are Apostrophe, Asterisk, Slash, Colon, Question Mark, Left Square Bracket, Backslash, Right Square Bracket.)

I went back to the person on YouTube and asked if cell E5 contained a date formatted as MM/DD/YYYY. By changing to WS.Name =Format(Range("E5").Value,"MM-DD-YYYY") the date contains dashes instead of slashes and the code worked.

Here is an explanation of the important parts of the code:

• On Error Resume Next tells Excel to not stop the macro when an error is encountered. The Err.Number and Err.Description will be available after the error is encountered.
• Err.Clear clears any previous error from the previous time through the loop.

By the way, it always fascinates me which characters *are* legal in a worksheet name. The figure below is =CHAR(3)&CHAR(6)&CHAR(7)&CHAR(6)&CHAR(4)

Every Friday, I will write about debugging fishy behaviour in Excel.

Title Photo: Mikali / Pixabay