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.

Posted in Uncategorized

27 thoughts on “Naming a Sheet Based on a Cell

  1. 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. 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. 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. 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. Macutan: The first sub, Workbook_SheetChange, goes in the ThisWorkbook module. The function, CleanSheetName, goes in a standard module (Insert > Module).

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

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

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

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

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

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

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

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

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

  18. Rochelle: The basics would look like this:

    Sub MakeSheetNames()
       
        Dim i As Long
       
        For i = 0 To 2
            With ThisWorkbook
                Sheets(i + 2).Name = .Sheets(1).Range(“A1”).Offset(i, 0).Value
            End With
        Next i
       
    End Sub
  19. Hello, the information was rather useful but I needed a solution that would A) clear the old cell names before hand (preventing the previously existing name error) as well as use dated naming format (JUN 27 for example). I’ve included the code below that needs to be added to do such a thing in case others need this as well (in my case, I was automating segments of time sheets and payroll to update each other).

    ‘this replaces the date format assignment
    Const sDATEFORM As String = “dd-mm-yyyy”

    ‘this goes towards the top in declarations
    Dim monthArray(0 To 11) As String
            monthArray(0) = “Jan”
            monthArray(1) = “Feb”
            monthArray(2) = “Mar”
            monthArray(3) = “Apr”
            monthArray(4) = “May”
            monthArray(5) = “Jun”
            monthArray(6) = “Jul”
            monthArray(7) = “Aug”
            monthArray(8) = “Sep”
            monthArray(9) = “Oct”
            monthArray(10) = “Nov”
            monthArray(11) = “Dec”

    ‘this goes before the assignment but after the formatting of the date
               Dim tmpStr() As String
                tmpStr = Split(sNewName, “-“)
                sNewName = monthArray(tmpStr(1) – 1) + ” “ + tmpStr(0)

    For those that might do what we do (store payroll dates on the first sheet, and have data on each following sheet page that represent biweekly time sheet totals and dates) this might be handy.

    Sub Workbook_SheetStuffUpdate()
            Dim i As Long
       ‘rename all 28 sheets to a generic unique name that we know won’t exist
      For i = 0 To 26
            Sheets(i + 2).Name = Sheets(i + 2).Name + “dave”
       Next i
       
       ‘Assign data from the first sheet to all the following sheets, this is based
      ‘on code provided above by Dick Kusleika (thanks!)
     
        For i = 0 To 26
                Sheets(i + 2).Cells(23, 9).Value = Sheets(1).Cells((i + 8), 2).Value
                Sheets(i + 2).Cells(7, 9).Value = Sheets(1).Cells((i + 8), 1).Value
        Next i
    End Sub

    As I said before, I based this partially on what I found here, and don’t want anyone to think I’m trying to take credit for anything (what little credit there is for such simple code).

  20. I realise this is quite old but I was browsing the web looking for a function to validate a sheet name. I was thinking a complex regex pattern would be required but I finally settled on my own function. This will work provided you do not intend on using the function at worksheet level…

    Function ValidSheetName(strSheetName As String) As Boolean
    On Error GoTo errHandler
    ThisWorkbook.Sheets(1).Name = strSheetName
    ValidSheetName = True
    ThisWorkbook.Sheets(1).Name = “Control”
    Exit Function
    On Error GoTo 0
    errHandler:
    ValidSheetName = False
    End Function

    Sub testFunction()
    Debug.Print ValidSheetName(“NameOK”)
    Debug.Print ValidSheetName(“NameNotOk/”)
    End Sub

  21. My code above was a straight froward copy and paste. In my instance I always want the sheet name to be ‘Control’. The code would be better if we were to simply restore the name.

    Function ValidSheetName(strSheetName As String) As Boolean

    Dim strThisSheet As String

        strThisSheet = ThisWorkbook.Sheets(1).Name

        On Error GoTo errHandler
            ThisWorkbook.Sheets(1).Name = strSheetName
            ValidSheetName = True
            ThisWorkbook.Sheets(1).Name = strThisSheet
            Exit Function
        On Error GoTo 0

    errHandler:
            ValidSheetName = False

    End Function

    Sub testFunction()

        Debug.Print ValidSheetName(“NameOK”)
        Debug.Print ValidSheetName(“NameNotOk/”)

    End Sub

  22. Last one, I promise… I’ve just discovered that this won’t return False if attempting to name the sheet history. That is it doesn’t raise the error, so we have to catch this in the code too:

    Function ValidSheetName(strSheetName As String) As Boolean

    Dim strThisSheet As String

        strThisSheet = ThisWorkbook.Sheets(1).Name

        On Error GoTo errHandler
            ThisWorkbook.Sheets(1).Name = strSheetName
            ValidSheetName = True
            ThisWorkbook.Sheets(1).Name = strThisSheet
            If UCase(strSheetName) = “HISTORY$” Then ValidSheetName = False
            Exit Function
        On Error GoTo 0

    errHandler:
            ValidSheetName = False

    End Function

  23. I have a worksheet where the name is in cell A130 and I just need the tab names when the cell changes or by running a macro, either way.


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.