atlas

Name your sheet tab same as cell value.

You can rename a worksheet in real time the moment you change a cell’s value.

A Worksheet_Change event can look at the entry, evaluate it for worthiness, and immediately rename the worksheet tab to be the same as what you entered in the cell.

There are 3 important considerations:
(1) The proposed name cannot be longer than the 31-character limit for sheet tab names.
(2) There cannot be any characters that violate sheet tab naming rules.
(3) The proposed worksheet name cannot be the same as an existing worksheet in that workbook because duplicate sheet names are not allowed.

For example, you want a sheet tab to be named the same as what you enter into cell A1.

To establish a Worksheet_Change event for the worksheet where you want this to happen, right-click that sheet tab, left-click to select View Code, and paste the following procedure into the large white area that is the worksheet module. Press Alt+Q to return to the worksheet.

Private Sub Worksheet_Change(ByVal Target As Range)
'Specify the target cell whose entry shall be the sheet tab name.
If Target.Address <> "$A$1" Then Exit Sub
'If the target cell is empty (contents cleared) do not change the sheet name.
If IsEmpty(Target) Then Exit Sub

'Disallow the entry if it is greater than 31 characters.
If Len(Target.Value) > 31 Then
MsgBox "Worksheet names cannot be more than 31 characters." & vbCrLf & _
Target.Value & " has " & Len(Target.Value) & " characters.", _
48, "Keep it under 31 characters."
Application.EnableEvents = False
Target.ClearContents
Application.EnableEvents = True
Exit Sub
End If

'Sheet tab names cannot contain the characters /, \, [, ], *, ?, or :.
'Verify that none of these characters are present in the cell's entry.
Dim IllegalCharacter(1 To 7) As String, i As Integer
IllegalCharacter(1) = "/"
IllegalCharacter(2) = "\"
IllegalCharacter(3) = "["
IllegalCharacter(4) = "]"
IllegalCharacter(5) = "*"
IllegalCharacter(6) = "?"
IllegalCharacter(7) = ":"
For i = 1 To 7
If InStr(Target.Value, (IllegalCharacter(i))) > 0 Then
MsgBox "You used a character that violates sheet naming rules." & vbCrLf & _
"Enter a name without the ''" & IllegalCharacter(i) & "'' character.", _
48, "Not a possible sheet name !!"
Application.EnableEvents = False
Target.ClearContents
Application.EnableEvents = True
Exit Sub
End If
Next i

'Verify that the proposed sheet name does not already exist in the workbook.
Dim strSheetName As String, wks As Worksheet, bln As Boolean
strSheetName = Trim(Target.Value)
On Error Resume Next
Set wks = ActiveWorkbook.Worksheets(strSheetName)
On Error Resume Next
If Not wks Is Nothing Then
bln = True
Else
bln = False
Err.Clear
End If

'If the worksheet name does not already exist, name the sheet as cell value.
'Otherwise, advise the user that duplicate sheet names are not allowed.
If bln = False Then
ActiveSheet.Name = strSheetName
Else
MsgBox "There is already a sheet named " & strSheetName & "." & vbCrLf & _
"Please enter a unique name for this sheet."
Application.EnableEvents = False
Target.ClearContents
Application.EnableEvents = True
End If

End Sub

This entry was posted in Tom's Tutorials for Excel and tagged , , , , , , , , , , , , , , , . Bookmark the permalink.

26 Responses to “Tom’s Tutorials For Excel: Name your sheet tab same as cell value.”

  1. Ali Dean says:

    Hi Tom,

    I realise it’s been a long time since this was posted so sorry about that!

    Is there a way to do with if the value of the cell in question is found using a formula?

    E.g. My cell takes it value from another worksheet in the workbook but it won’t update the sheet name when I update the other worksheet.

    Do you have any way around this?

    Cheers

    Ali Dean

    • Avatar of Tom Urtis Tom Urtis says:

      Use the Calculate event in your worksheet module for that.

      Suppose the worksheet whose tab you want to change is codename Sheet1. NOTE: Codename is NOT necessarily the same as tab name. Take a look at this link to see what a codename is if you are not sure.

      http://www.atlaspm.com/toms-tutorials-for-excel/toms-tutorials-for-excel-vba-help-usually-right-sometimes-wrong/

      Now then, let’s further say on your Sheet1 codename worksheet, that cell A1 contains the formula which, when it changes, causes the Sheet1 codename to have a new tab name, of, say My Budget or whatever is entered in that other source sheet which the formula in cell A1 depends on.

      In your worksheet module, in its simplest form, this would do what you want:

      Private Sub Worksheet_Calculate()
      Sheet1.Name = Range(“A1″).Value
      End Sub

  2. Mark Gosnell says:

    I am trying to get VBA to write the following formula to an Excel 2007 Worksheet cell:

    ActiveCell.FormulaR1C1 = “= Concatenate(“Pg. “,’Worksheet1′!$I$2)”

    not sure how to get around the “Pg. ” issue? Any help would be appreciated.

  3. Saleh says:

    Hi Tom,

    I know it long time passed from this topic but I used this macro and my problem is: I should only write in A1 cell to change the sheet name. I mean when I copy & paste data from another sheet to A1 cell it is not work! how can I fix the problem because most of the time data need to be pasted on the exact sheet.

    Best regards
    Saleh

  4. T. Reed says:

    Hi Tom

    I just copied and paste your code to VBS module and it works. Thank you very much. However, it only works for 61 sheets and not the rest (there are 178 sheets in my workbook).

    Thanks very much in advance.

    • Avatar of Tom Urtis Tom Urtis says:

      Hi and thanks for your message. I cannot understand how the code does not look at every worksheet in your workbook for a duplicate sheet tab name. It works fine when I tested it. Some numbers are significant to Excel and to computers, especially those with a factor of a power of 2 such as 8^2 and so on. The number 178 is not such a significant number and that many worksheets in a workbook is not excessive. Can you try the code again, maybe on some other sheet like the 137th and see what happens.

  5. Mark Gosnell says:

    I am using the INDIRECT command to access the material data I need on another worksheet in my workbook. The problem I am having is if I have a space in the worksheet name AL 7050 T7451 425 Plate the INDIRECT command returns #REF!.

  6. Andy says:

    I am very new to VBA and I am trying to build a a multitab template that has two pre-existing Tabs called Cover and List. The following Tabs (5 or 6, pre-existing, with some calculations in it) are to be named from the Tab Cover (whose Tab name is automatically taken from it’s cell A1 using your original code above) but from different cells (i.e. B16, B18, B20….etc). These other Tabs are called Sheet3, Sheet4 etc. Is there any way to modify your code to do this?

    I tried to copy B16 into Sheet3!A1 and put the original code again in its code but it seems to react only to changes that are not fed by a formula leading to another tab. Since, as you explained, this is not a real macro, I am not even able to run it manually or assign it to a button.

    I looked at the other events that can be used as triggers (rather than Change) but none seems to be good for this use (I tried to fiddle with FollowHyperlink but I think I need a little bit more studying VBA before I can test at random :-( ).

    Would really appreciate your help. I learned a lot already by observing the logic you used to produce the original routine. I found others in internet but none was as cleaver and complete as yours.

    Thanks
    Andy

    .

    • Avatar of Tom Urtis Tom Urtis says:

      Thanks for following my Excel page.

      I will assume you are wanting changes made to certain cells on the Cover sheet, and only on the Cover sheet, to be what causes other sheets to have their tab names changed.

      I further assume your sheet named Cover is VBA sheet code name of Sheet1, and that the List sheet’s code name is Sheet2. And I’ll assume that a change to cell A1 in the Cover sheet will change that sheet name, and changes to cells B16, B18, B20, and B22 on the Cover sheet will change the tab names for sheet code names Sheet3, Sheet4, Sheet5, and Sheet6 respectively.

      If by chance you do not know what I mean by “sheet code name”, go into the VBE and hit Ctrl+R. Find the name of your workbook in the Project – VBAProject window. your workbook name will be in bold and look like VBAProject (YourWorkbookName). Expand the yellow folder named Microsoft Excel Objects. You will see that each worksheet has two names. One is the code name, and the other is the name you would see on the tab of the sheet. For example, one sheet (I am assuming) will be named Sheet1 (Cover). So, modify the belwo code in the Select Case block for the proper sheet code names of the worksheets you want to change the names of.

      This worked when I tested it, given the aforementioned assumptions. It goes in the module of the Cover sheet **in place of** (that is, substitute this code with) the previous original code you pasted in from my posted example.

      Private Sub Worksheet_Change(ByVal Target As Range)
      ‘Specify the target cell whose entry shall be the sheet tab name.
      If Intersect(Target, Range(“A1,B16,B18,B20,B22″)) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
      ‘If the target cell is empty (contents cleared) do not change the sheet name.
      If IsEmpty(Target) Then Exit Sub

      ‘Disallow the entry if it is greater than 31 characters.
      If Len(Target.Value) > 31 Then
      MsgBox “Worksheet names cannot be more than 31 characters.” & vbCrLf & _
      Target.Value & ” has ” & Len(Target.Value) & ” characters.”, _
      48, “Keep it under 31 characters.”
      Application.EnableEvents = False
      Target.ClearContents
      Application.EnableEvents = True
      Exit Sub
      End If

      ‘Sheet tab names cannot contain the characters /, \, [, ], *, ?, or :.
      ‘Verify that none of these characters are present in the cell’s entry.
      Dim IllegalCharacter(1 To 7) As String, i As Integer
      IllegalCharacter(1) = “/”
      IllegalCharacter(2) = “\”
      IllegalCharacter(3) = “["
      IllegalCharacter(4) = "]”
      IllegalCharacter(5) = “*”
      IllegalCharacter(6) = “?”
      IllegalCharacter(7) = “:”
      For i = 1 To 7
      If InStr(Target.Value, (IllegalCharacter(i))) > 0 Then
      MsgBox “You used a character that violates sheet naming rules.” & vbCrLf & _
      “Enter a name without the ”” & IllegalCharacter(i) & “” character.”, _
      48, “Not a possible sheet name !!”
      Application.EnableEvents = False
      Target.ClearContents
      Application.EnableEvents = True
      Exit Sub
      End If
      Next i

      ‘Verify that the proposed sheet name does not already exist in the workbook.
      Dim strSheetName As String, wks As Worksheet, bln As Boolean
      strSheetName = Trim(Target.Value)
      On Error Resume Next
      Set wks = ActiveWorkbook.Worksheets(strSheetName)
      On Error Resume Next
      If Not wks Is Nothing Then
      bln = True
      Else
      bln = False
      Err.Clear
      End If

      ‘If the worksheet name does not already exist, name the sheet as cell value.
      ‘Otherwise, advise the user that duplicate sheet names are not allowed.
      If bln = False Then
      Select Case Target.Address
      Case “$A$1″: Sheet1.Name = strSheetName
      Case “$B$16″: Sheet3.Name = strSheetName
      Case “$B$18″: Sheet4.Name = strSheetName
      Case “$B$20″: Sheet5.Name = strSheetName
      Case “$B$22″: Sheet6.Name = strSheetName
      End Select
      Else
      MsgBox “There is already a sheet named ” & strSheetName & “.” & vbCrLf & _
      “Please enter a unique name for this sheet.”
      Application.EnableEvents = False
      Target.ClearContents
      Application.EnableEvents = True
      End If

      End Sub

  7. Carl Witthoft says:

    Never mind — I missed the point that this sheet code autoruns when the cell is edited.

  8. Carl Witthoft says:

    Hi – strange problem: Whether I load the code into a workbook Module or into the Worksheet Code, I cannot convince Excel 2010 that the macro exists. It doesn’t show up in the list of macros to run, and in the VB editor window, hitting “Run” or”Debug” does nothing at all. All other macros I have are working.

    • Avatar of Tom Urtis Tom Urtis says:

      Thanks for following my Excel page.

      What I posted is an example of a Worksheet level event procedure. These kinds of procedures are not macros per se, and they would never show up in the Macro dialog as a list of macros to run. That is because, being an event procedure, they are triggered by an event of some kind. In this case, the event is a Change event. A Change event happens when a change to a cell is made, typically when you enter a value into a cell, or you delete the contents of the cell, or basically when you change the data (that is, type something new, edit something existing, or delete what is in) a cell. Further, the change event in my example is only (as you can see by the first line of code in the procedure) limited to cell A1. It is cell A1 that is being monitored for a change on that particular worksheet, and no other cell. So, type some text into cell A1, such as Hello and (unless there is another sheet tab in that workbook coincidentally also named Hello), your sheet tab for that sheet will be automatically named Hello. Also, be sure you followed my direction in the example. I never said to put he procedure I posted into the Workbook module. It goes into the Worksheet module as I wrote in my paragraph immediately above the procedure code.

  9. Avatar of Tom Urtis Tom Urtis says:

    On your main sheet, what exactly is the drop down list? Is it data validation? A combobox from the Forms toolbar? A combobox from the activex Toolbox? Is it entered into a cell? If so, what is the cell address? Whatever the case, I would tie the code as a macro to the drop-down selection of the sheet name, with an inputbox that pops up to ask for the new name of the sheet you selected in the drop down. Please provide a few specific details such as I asked for, and I’m sure a solution is possible.

  10. Rossei says:

    Hello,

    This code is exactly what I was looking for. Thanks.

    But if I the user leaves the target cell empty or delete the entry, I want a msgbox to pop-up and ws name to go back to its original code name or something specific.

    Your code:
    ‘If the target cell is empty (contents cleared) do not change the sheet name.
    If IsEmpty(Target) Then Exit Sub

    I tried this instead:
    ‘If the target cell is empty, show this msg
    If IsEmpty(Target) Then
    ActiveSheet.Name = “Sheet1″
    MsgBox “Sheet name will presume.”
    Exit Sub
    End If

    doesn’t work, it still keeps whatever was input before.

    • Avatar of Tom Urtis Tom Urtis says:

      Thanks for following my page. You said in your comment that your amended code does not work for you, but I tested your sample code modification and it works for me. Maybe you are testing it in a module for a worksheet other than an existing worksheet whose tab is already named Sheet1. At the point of your modified code insertion, there are no safeguards yet on place for duplicate sheet names, so maybe you are hard-coding the default name of Sheet1 when another actual Sheet1 worksheet exists. In any case, based on your question and attempted workaround, I cannot repeat your error because your code works in the context you described.

      • Rossei says:

        Hey Tom,

        Thanks for your prompt reply. Mine wasn’t working because I was deleting the cell entry. If the entry is backspaced, then it changes the tab name whatever I want.

        My Code:
        ‘If the target cell is empty, sheet name will remain same.
        If IsEmpty(Target) Then
        ActiveSheet.Name = ActiveSheet.CodeName
        MsgBox “Sheet name will presume.”
        Exit Sub
        End If

        Now, I want to add another function for ‘deletion’. Like IsEmpty. So, if the user hits ‘Delete’ from keyboard for the target cell, active sheet would presume codename.

        I appreciate your help.

  11. jeffC says:

    Tom
    how can this code be modified to cover any changes of name in any worksheet in a workbook?

    • Avatar of Tom Urtis Tom Urtis says:

      You’d place the procedure in the workbook module for a workbook-level sheetchange event. Example, substitute the procedure name
      Private Sub Worksheet_Change(ByVal Target As Range)

      with
      Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

      To easily access your workbook module, in Excel version 2003 or before, find the little Excel workbook icon near the upper left corner of your workbook window, usually just to the immediate left of the File menu option. Right-click on that icon, and left-click to select View Code. In Excel versions 2007 or after, from your worksheet press Alt+F11, then press Ctrl+R, find your workbook name in the “Project – VBAProject” left vertical pane, expand the Microsoft Excel Object folder for your workbook, right click on ThisWorkbook and left-click to select View Code. Paste the procedure into the large white area that is the workbook module, then press Alt+Q to return to the worksheet.

  12. Troy says:

    Is it possible to do this with the cell reference (ie. $A$1) being on a different worksheet? I’ve tried referencing the usual way but it doesn’t work.

    • Avatar of Tom Urtis Tom Urtis says:

      Supposing for example you want to change the name of the Sheet1 tab to some other name, and your A1 target cell is in, say, Sheet3 , place the code I posted into the Sheet3 module (or whatever sheet you are working on). Then, simply change this line
      ActiveSheet.Name = strSheetName
      to this
      Worksheets(“Sheet1″).Name = strSheetName

      My only caveat would be to not code for the existing and soon-to-be-renamed tab name but the sheet’s VBA object name, example
      Sheet1.Name = strSheetName

  13. Biray Kocak says:

    Hi,
    Congratulations about above code. it’s very useful.

    with a small addition, (adding “&” after MsgBox “You used a character that violates sheet naming rules.” ) i could run it.

    But, i have a bug when i want to use it with a formula in a cell. two things:

    1) when you put a formula in cell a1, if the value changes, sheet name does not change automatically. is this due to vba running method?

    2) when you put a formula in cell a1, you can change sheet name by correcting cell by F2 and enter it. but, if value would not be changed as a result, excel generates this error (there is already a sheet named. please enter a unique name for this sheet). then excel deletes formula in this cell.

    • Avatar of Tom Urtis Tom Urtis says:

      Thank you for pointing out that an ampersand “&” was missing, which I have corrected.

      To answer your first question, because the event I posted is a Change event, a formula usually would not be the trigger for changing the sheet tab name. However, you could use a Calculation event that captures the change in a cell’s value that is returned by a formula.

      I say “usually” because with the Change event as I posted it, entering the formula =Hello changes the sheet tab name to Hello. But I am sure you are asking about a different scenario whereby the formula in (for example) cell A1 is conditional or relies on precedent cells.

      To answer your second question, you could establish a Calulation event that is triggered by the calculation of the cell of interest, without deleting the formula in cell A1.

      Private Sub Worksheet_Calculate()
      With Range("A1")
      If Len(.Value) = 0 Or Len(.Value) > 31 Then Exit Sub
      
      Dim IllegalCharacter(1 To 7) As String, i As Integer
      IllegalCharacter(1) = "/"
      IllegalCharacter(2) = "\"
      IllegalCharacter(3) = "["
      IllegalCharacter(4) = "]"
      IllegalCharacter(5) = "*"
      IllegalCharacter(6) = "?"
      IllegalCharacter(7) = ":"
      For i = 1 To 7
      If InStr(.Text, (IllegalCharacter(i))) > 0 Then
      MsgBox "The formula in cell A1 returns a value containing a character that violates sheet naming rules." & vbCrLf & _
      "Recalculate the formula without the ''" & IllegalCharacter(i) & "'' character.", _
      48, "Not a possible sheet name !!"
      Exit Sub
      End If
      Next i
      
      Dim strSheetName As String, wks As Worksheet, bln As Boolean
      strSheetName = (.Text)
      On Error Resume Next
      Set wks = ActiveWorkbook.Worksheets(strSheetName)
      On Error Resume Next
      If Not wks Is Nothing Then
      bln = True
      Else
      bln = False
      Err.Clear
      End If
      
      If bln = False Then
      ActiveSheet.Name = strSheetName
      ElseIf ActiveSheet.Name  <> .Text Then
      MsgBox "There is already a sheet named " & strSheetName & "." & vbCrLf & _
      "Recalculate the formula in cell A1 to return a unique name."
      End If
      
      End With
      End Sub
      • Des K says:

        Hi Tom,

        Thanks for the updated Worksheet_Calculate code. It almost works for me, but not quite – the sheet tab names I want changed (‘target’ sheets) depend on a selection in a ‘main’ sheet. So, after putting the above code in a target sheet whose tab name I need changed, I go to the ‘main’ sheet to a drop down list to choose the required selection, which then automatically makes a change in cell A1 of the target sheets – as the above code you wrote works on the ActiveSheet, the code changes the tab name of my main sheet (as this is where I go to make my selection which activates the change in A1 of the target sheet based on a formula), rather than my target sheet. Is there any way you can amend the above code so that my target sheet tab name changes rather than my main sheet tab name? I’m very new with VBA, so if you could list out the exact changes that I would need to make, that would be greatly appreciated! Thank you.


Leave a Reply