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
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
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.
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
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.
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
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
Macutan: The first sub, Workbook_SheetChange, goes in the ThisWorkbook module. The function, CleanSheetName, goes in a standard module (Insert > Module).
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.
–
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
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
Robert: See here
http://www.dicks-blog.com/archives/2004/06/14/sheet-name/
Aha thanks
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
That’s right Julian. Now how the heck did I miss that?
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
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?
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)
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!
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
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
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
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.
Rochelle: The basics would look like this:
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
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).
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.
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).
Excel sheets are limited to 31 characters, so I changed the last line of the cleaner to
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
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.
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
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:
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