Naming a Sheet Based on a Cell

Ian asks how to name a sheet based on the contents of a cell. That is, when the cell's contents change, the sheet's name changes. It's a pretty simple operation in vba:

Sh.Name = Target.Value

in the Change event. But you know it's not that easy. There are some characters that cannot be used in a sheet name and at least one word, history, is reserved. So the majority of the sub is error checking. Here's my attempt at it:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
 
    Dim sNewName As String
    Dim sMsg As String, sEndMsg As String
    Dim sTitle As String
   
    Const sDATEFORM As String = "yyyymmdd"
    Const sNUMFORM As String = "#0.00"
   
    'Set the title for the message box
    sTitle = "Invalid Sheet Name"
 
    'Make sure it's the cell we want
    If Target.Address = "$A$1" Then
       
        'Account for specific data in the cell
        If IsDate(Target.Value) Then
            sNewName = Format(Target.Value, sDATEFORM)
        ElseIf IsNumeric(Target.Value) Then
            sNewName = Format(Target.Value, sNUMFORM)
        Else
            'sNewName = Target.Value
            'thanks, Charlie
             sNewName = Cstr(Target.Value)
        End If
       
        'Get rid of illegal or unwanted characters
        sNewName = CleanSheetName(sNewName)
       
        'Create the end of the prompt for the message box
        sEndMsg = vbNewLine & vbNewLine & "The sheet name will not be changed." & _
            vbNewLine & vbNewLine & "Sheet name attempted: " & sNewName
           
        'Establish error checking
        On Error Resume Next
            'Attempt to rename the sheet
            Sh.Name = sNewName
           
            'If there's an error
            If Err.Number <> 0 Then
               
                'Be more descriptive for a certain error, otherwise
                'return the error that Excel returns
                If Left(Err.Description, 19) = "Application-defined" Then
                    sMsg = "You entered an invalid sheet name." & sEndMsg
                Else
                    sMsg = Err.Description & sEndMsg
                End If
               
                'Display the error
                MsgBox sMsg, vbOKOnly, sTitle
               
            End If
        On Error GoTo 0
    End If
   
End Sub

This sub is in the ThisWorkbook module so that it applies to all sheets in the workbook. Whenever A1 is changed in any sheet, it will attempt to change the sheet's name. The sub uses this function to clean out unwanted characters.

Public Function CleanSheetName(ByVal sOldName As String, _
    Optional sReplacement As String = "_") As String
 
    Dim vaIllegal As Variant
    Dim i As Long
    Dim sTemp As String
   
    sTemp = sOldName
    'List unwanted characters
    vaIllegal = Array(".", "?", "!", "*", "/", "", "[", "]", "'")
   
    'Make sure replacement isn't illegal
    For i = LBound(vaIllegal) To UBound(vaIllegal)
        If sReplacement = vaIllegal(i) Then
            sReplacement = "_"
            Exit For
        End If
    Next i

    'Replace all illegals with the replacement
    For i = LBound(vaIllegal) To UBound(vaIllegal)
        sTemp = Replace(sTemp, vaIllegal(i), sReplacement)
    Next i
   
    CleanSheetName = sTemp
   
End Function

I'm sure I missed something, so let me know when you find it.

20 Comments

  1. Frank Kabel:

    Hi Dick
    personally I'd add
    application.enableevents=false
    before changing the name and
    application.enableevents=true
    afterwards (maybe included with an error handler)

    Otherwise this name change could trigger the calculation event (and this may not be desired)

    Frank

  2. Paul Hoch:

    What if the sheet name already exists as another sheet? Would you want to cycle through the sheets in the active workbook to determine if the cell value is already a sheet name to prevent this? You also probably would want to check to find out if there are more characters then are valid. I forget what the limit is - 39? Anyway, you could certainly put more text in a cell value than would be valid for a sheet name so it would probably be prudent to ensure that the string isn't too long.

  3. Charlie:

    Hi Dick,

    Code breaks at :

    >Else
    > sNewName = Target.Value
    >End If

    if the cell contains an error value. You just need something like:

    elseif IsError(Target.Value) then
    sNewName = "Error"

    Regards

    Charlie

  4. Macutan:

    is it just me, or i cannot figure out how to get this to work...

    Do i copy both subs into a module? or into: ThisWorkbook...

    quite new at VBA, would appreciate any help.

    rgds

  5. Dick:

    Macutan: The first sub, Workbook_SheetChange, goes in the ThisWorkbook module. The function, CleanSheetName, goes in a standard module (Insert > Module).

  6. Jamie Collins:

    Dick, The following characters which you have as illegal appear to be valid in a worksheet name:

    ".", "!", "[", "]", "'"

    e.g. I just successfully name a sheet as .![]'a

    Jamie.

    --

  7. Dick:

    Jamie - vaIllegal is a misnomer. The characters are legal, just not advised. There are situations where the programs tries to parse the workbook/worksheet names and things get screwy. Notice these charaters are all found in an external reference

    ='C:\Dick\[Bankrec.xls]Sheet1'!$B$1

  8. Robert Linggoputro:

    Is there a way to do a reverse thing? I mean I name the sheet and I need those name sheet show up in particular cell?

    Thanks

  9. Dick:

    Robert: See here

    http://www.dicks-blog.com/archives/2004/06/14/sheet-name/

  10. Robert Linggoputro:

    Aha thanks

  11. Julian:

    Hi - I think ':' is another illegal char for a sheet name.

    Many thanks for all the hints- great for someone knows what they want to do but not how to do it in VBA :-)

    Julian

  12. Dick Kusleika:

    That's right Julian. Now how the heck did I miss that?

  13. Amit:

    Hi,

    I'm trying to name a worksheet "dependent" on the name of a previous one, e.g. if the previous worksheet name is UPDATE 8 I want my code to insert a new worksheet and call it UPDATE 9.

    Can you help???

    Thanks,

    Amit

  14. Nate:

    How do I reverse this operation, naming a sheet based on a cell, and make the values of any given cells equal to the sheet names in the workbook. Can I do this operation without running a macro?

  15. Jeremy:

    To make the cell value equal to any sheet name, try this:

    Put this function in a normal Module:

    Function Mysheetname(shindex As Integer)
    Application.Volatile
    Mysheetname = Sheets(shindex).Name
    End Function

    Then put this formula in your worksheet in any cell:
    =Mysheetname(3)

    (substitute the number in the formula for the sheet location)

  16. Amber Laws:

    I tried the code above, and nothing happened. Well, nothing on the surface anyway. I believe however, that this is because the cell I am using (C7) on each sheet contains a formula, so that when the base value is entered in a certin cell on a front page like sheet, it is automaticlly changed on all sheets. I am not sure why this is not calling the Change event as the value is changing, but....I don't know, any insight will be helpful. Prior to finding this article I had used different code, stored in each worksheet's module, that changed the worksheet's name when a different cell was selected on that sheet (Worksheet_SelectionChange event). It worked great, but it was a mighty pain to select each of the 31 worksheets and then select any new cell in the sheet. This seemed a good solution, but I have hit a brick wall. Please Advise.

    Thanks!

  17. Kelly:

    I am new to VBA like Macutan - so I really appreciated the way you broke it down and said where to put the sub and function.
    I am creating different tabs for each month so I just adjusted how the program displays dates and it worked like a charm. Beautiful and thanks :D

  18. Jason:

    Hi, Using an activex form control to change the content of the cell. This doesn't trigger the thisworkbook sheetchange ... ? Can confirm it works if the cell is changed manually.
    Jason

  19. Damien:

    Hi, I am new to VBA as well and i need something slightly different.

    Instead of naming a sheet based on a cell, i need to call out a sheet from a workbook based on a cell input.

    please help

  20. Rochelle Walker, Sr.:

    All this is great, but I need to rename worksheet tab names by referencing different cells in another worksheet within the same workbook. In other words, I have Sheet1 with data in cells A1, A2, A3, etc. I want the data in A1 to be the name of Sheet2, the data in A2 to be the name of Sheet3, the data in A3 to be the name of Sheet4, etc. Please help.

Leave a comment