Tom’s Tutorials For Excel: Name your sheet tab same as cell value.


Share Button

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

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,
53 comments on “Tom’s Tutorials For Excel: Name your sheet tab same as cell value.
  1. 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.

    • TomU 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.

  2. 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.

    • 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

  3. jeffC says:

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

    • 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.

  4. 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.

    • 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.

  5. 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.

  6. 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.

    • 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.

  7. Carl Witthoft says:

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

  8. 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

    .

    • 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

  9. 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!.

  10. 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.

    • 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.

  11. 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

  12. 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.

  13. 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

    • 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

  14. AJH says:

    From your original post. Is there a way to change your target cell to take say (3) cells and combine them to name a new tab. For example: A1: type1, A2: type2, A3: type3 and format them to show in the tab with spaces and punctuation?

  15. miscurrie says:

    Hi There

    I have found this, so sorry I know that it is quite an old post. I am trying to get worksheets to be named from a list. The list is a list of student names that will change from term to term etc. So when I change the student name I would like the sheet name to change. Currently I just have student 1, student 2, etc in individual cells. So there could be up to 25 or 30 students and therefore worksheets…

    • Tom Urtis says:

      A few considerations:
      Do you want a new worksheet to be created as the names are looped through, or do the sheets already exist.
      If a sheet for a name on the list already exists, should it be re-created or kept and appended.
      Where is this list of names (sheet and range)
      Any duplicate names, if so what to do about that, as 2 worksheets with the same names cannot reside in the same workbook.

      • Miscurrie says:

        Hi there

        I am not sure if my original reply went through, however it is not up here anymore so I will try again.

        I don’t want new worksheets created, they will be existing. I am creating a reading log for data entry, and will just have a template on each worksheet ready to go.

        The list of names is in a separate worksheet called “Class List” which I would like to remain called ‘Class List”. The list is student names and are individual cells and I have allowed for up to 30 students in the list. There should be no duplicate names as I will use a number or something to that effect as I need to know which student is which.

        Thanks for you help it is much appreciated.

        Kind Regards

        • Tom Urtis says:

          I asked you for the range where the list of names is, but you still have not answered.

          Also, if there are more sheets in the workbook besides Class List, how would the code know to not name those sheet tabs.

          Have you checked to make sure that the mystery range of 30 cells on the Class List sheet do not contain student names that are longer than 31 characters. You can use data validation for those cells.

  16. miscurrie says:

    Hi there

    Thank you for your response!

    The worksheets will be existing (as they are logs for recording data. I thought it would be best to have the template there rather than creating a worksheet every time and try and put the template in).

    The students names are currently on one worksheet called “Class List” in the same workbook, (which I would like to remain “Class List”) and I have allowed for up to 30 students in my list.

    Currently I have the list as “Student 1” to “Student 30”, in individual cells. So when we input the students name, I will just put in “Jane Smith” (for e.g.) in place of “Student 1” for example. There should be no double up’s with names as if there ever are two kids with the same name we usually put a 1 or 2 or whatever on the end to distinguish.

    I hope I have explained that ok. As you can no doubt tell, I am not a tech person.

    Thanks again for your help. Appreciated.

    Cheers

  17. Tina says:

    Hello,
    thank you for your information.
    I am just wondering whether I can name a sheet tab name as a cell value in a different sheet?

    Cheers.
    Tina

    • Tom Urtis says:

      Hello Tina, yes this can be done. Hit Alt+F11 to get into the VBE. From there. hit Ctrl+R to show the Project window, where you’ll see the name of your open workbook(s) in bold, such as VBAProject(“YourWorkbookName”). Expand that workbook object and you see a yellow folder named Microsoft Excel Objects. Expand that folder and you see the worksheet object names and the ThisWorkbook module. For example, suppose you have two worksheets in your workbook, where one worksheet has the tab name of Budget and the other has the tab name of Expenses. Suppose you want the worksheet named Budget to change to whatever is in cell A1 of the Expenses worksheet. In the VBE, pay particular attention to the object name of the worksheet that you want to change the tab name for. You know its tab name is Budget at the moment. However, suppose its object name is Sheet1, which you would see in the Project window as Sheet1(Budget). Maybe in your workbook the recipient worksheet (whose tab you want to change) is the sheet object named Sheet5(Budget) or Sheet23(Budget). Whatever it is, make a note of the object name of that sheet. Let’s say its object name is Sheet1. Now, in the Project window, double-click on the sheet name that holds the value in its cell A1 that, when it changes, you want the Budget tab to be changed to. As I said, maybe that worksheet is named Expenses, but whatever it is named, in the worksheet module of that sheet, put in this Change event code:

      Private Sub Worksheet_Change(ByVal Target As Range)
      If Target.Address = “$A$1” Then Sheet1.Name = Range(“A1”).Value
      End Sub

      If cell A1 gets its value changed as the result of a formula, you’d use the Calculate event:

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

      Of course, there are additional monitors for length (31 characters or less) and illegal sheet naming characters as I posted in my example, but this is the general approach for how a Change event on one worksheet can affect another worksheet.

      • Tina says:

        Thank you, Tom.

        In my case, I want to automatically change the name of Sheet 2 to a child ID inputted in Sheet 1 (say, B2).

        Do I open the VBE and select Sheet 1 and then put in the Change Event code? Like:

        Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Address = “$B$2” Then Sheet2.Name = Range(“B2”).Value
        End Sub

        When I did so, it does not seem to change anything. Excel said ‘ambiguous title’ or something, but the warning does not show up anymore.

        I need to protect sheet 2. Does it affect anything?

        I am such a VBA beginner. Thanks for your help.

        Tina

        • Tom Urtis says:

          Given the scenario you outline, the code would go into the Sheet1 module because it is cell B2 on Sheet1 that is being inputted and hence changed.

          The code would be (assuming the VBA object code name for the worksheet whose tab name is currently Sheet2 but which you want to change), then in the Sheet1 module goes this:

          Private Sub Worksheet_Change(ByVal Target As Range)
          If Target.Address = “$B$2” Then Sheet2.Name = Target.Value
          End Sub

          • Tina says:

            I did what you said but the Excel says ‘Compile error: Ambiguous name detected: Worksheet_Change.’

            It is probably because I have other code above starting with the same first line
            Private Sub Worksheet_Change(ByVal Target As Range).

            How do I solve that?

            Even though I deleted my previous codes and typed in your codes, the Excel says ‘syntax error.’

            Heaps of thanks

          • Tina says:

            Or Is there any way to change tab name to a cell value on a protected sheet?

            In my case, people input a child ID in Sheet 1 (say B2), and this Child ID will be linked to a cell in sheet 2 (say A2). The second sheet is protected. I want the name of Sheet 2 to change with the Child ID.

            How do I solve this issue?

            Sorry for so many questions.
            Thanks in advance

          • Tom Urtis says:

            Everything you want to do is doable. The compile error is due to the same event being coded more than once in the same module, violating VBA’s rule about that. Post all your sheet module code so I can see what the other error is. Regarding sheet protection, that is irrelevant for what you are doing with sheet tabs. I already gave you the answer about how to change the tab name of one sheet when another sheet’s cell is changed. If “linked to a cell in Sheet2” means the cell in Sheet2 gets its value from a formula in that cell, then you can alternatively use the Worksheet Calculate event in the Sheet2 module to monitor and change the Sheet2 tab name when that cell’s value gets recalculated.

  18. Bruno says:

    Hi Tom. I wonder if you could help. The code works great, but the cell I want to “name” the tab is on another tab called data. Cell A6 to be exact. How can I change the code in a way it will search for that specific cell. Thank you

    • Tom Urtis says:

      Paste the code I posted into your data worksheet module. Change the target cell references (there are a couple of them) from A1 to A6 in the code.

      Next, you need to reference the worksheet whose tab name you want to change. To do that, in the VBE hit CTRL+R to show the Project window. Expand the name of your workbook and then expand the Objects folder. Worksheets are objects that have 2 name properties. One name property is is CodeName which never changes. The other name property is its Name which is what you see on its tab. For example, maybe the data sheet is named data on its tab, but Excel regards that sheet object as Sheet2. You would know that by looking at the list of objects and see (for example)
      Sheet2(data)
      Find the worksheet in the object list that is the worksheet whose tab you want to change when cell A6 is changed on the data sheet. Maybe the worksheet’s CodeName is Sheet5. In my code change this line
      ActiveSheet.Name = strSheetName
      to this
      Sheet5.Name = strSheetName

      • Bruno says:

        Genius! Works great. But I need this to happen with 5 more sheets (6 in total).
        The values in cells A6:A11 should define the names of sheets 1 to 6.
        Should I just had the code you gave for each of the sheets or is there a way to keep the code small and just insert the missing parts?
        Anyway, I am really amazed with your work. Thank you

        • Tom Urtis says:

          Replace the code you have with this, that goes into the module of the worksheet where you are changing the values in cells A6:A11. Works when I tested it.

          Private Sub Worksheet_Change(ByVal Target As Range)
          ‘Specify the target cell whose entry shall be the sheet tab name.
          If Intersect(Target, Range(“A6:A11”)) 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

          Dim ws As Worksheet, cn As Integer, tRow As Long
          tRow = Target.Row – 5
          For Each ws In Worksheets
          cn = Val(Right(ws.CodeName, Len(ws.CodeName) – 5))
          If cn = tRow Then
          ws.Name = Target.Text
          Exit For
          End If
          Next ws

          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

  19. Bruno says:

    And that does it! Thank you for your help. Greatly appreciated!

  20. Ryan B. says:

    Hi Tom, I used your formula, but when I close the Excel file and open it again the code disappears, what should I do?

  21. Brandon R. says:

    I seem to be having trouble finding a solution to this, hopefully you can help.
    I need to rename existing worksheets 4 though 10 to the contents of cells B14, B29, B44, B59, B74, B89, and B104 from worksheet 2. Sheet4 would be the contents from B14, sheet5 would be B29, etc. I also need the macro to run anytime those cells are modified.

  22. Jamie G says:

    Hi Tom,

    Can you simplify this code for me? I have a macro that duplicates a template that I have created and then renames the tab based on a list. What I need is this:

    I could like to put in a macro that does not allow the user to enter any names into the list that violate the rules of naming a worksheet. I know that it already exists in the code that you have provided, I just don’t know VBA well enough to pull out what I need. My list is in column A in a worksheet called “opportunity pipeline”. Can you give me a modified code to accomplish this?

    Thank you!

    • Tom Urtis says:

      If I understand your set-up correctly, you want to monitor a list of allowable worksheet names that are listed in column A. In that worksheet’s module, with the code I posted, it might be as simple as changing this line:

      If Target.Address <> “$A$1” Then Exit Sub

      to this:

      If Target.Column <> 1 Then Exit Sub

      If there’s more to what you need for this to work with your sutuation, please post back.

  23. Jamie G says:

    Ok, so the code below is what I used. Its not working. I already have a macro that duplicates a tab and renames the worksheet, so I do not need this macro to do that. I simply want this macro to let the user know that they cant use certain characters in the cell and it must be under 31 characters. So what is wrong with this?

    [code]
    Private Sub Worksheet_Change(ByVal Target As Range)
    ‘Specify the target cell whose entry shall be the sheet tab name.
    If Target.Column A 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

    End Sub
    [/code]

  24. Jamie G says:

    Oh also, I tried it with Target.Column 1 like you originally said, but that didn’t work so I was experimenting with “A”. Thank you!

    • Tom Urtis says:

      This code does exactly what you say you want. I just tested it and it works without any problem. Keep in mind this is technically not a macro but a procedure, specifically a Change event in the worksheet module, and only one Change event is allowed in a module. If you already have a Change event in your sheet module, you need to combine that one and this one into a single Change event procedure.

      [code]

      Private Sub Worksheet_Change(ByVal Target As Range)
      If Target.Column <> 1 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

      End Sub

      [/code]

  25. Vincent says:

    Thank you very much for your code!

Leave a Reply

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

*