Spreadsheet: 195871d1353698294-possible-to-change-sheets-names-automatically-based-on-cell-values-in-other-sheets-sheetname-from-cell-automatically
Sheet: Sheet2

http://www.dailydoseofexcel.com/archives/2005/01/04/naming-a-sheet-based-on-a-cell/
Home
About
Consulting
Books
Blogroll
Contact
« Comparing Numbers
Update To PopupMenu »
Naming a Sheet Based on a Cell
January 4, 2005, 12:54 pm by [NAME]
[NAME] 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 [NAME]:
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 [NAME]
Dim sMsg As [NAME], sEndMsg As [NAME]
[NAME]
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, [NAME]
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 [NAME].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 [NAME], _
Optional sReplacement As String = “_”) As [NAME]
Dim vaIllegal As [NAME]
Dim i As Long
Dim sTemp As [NAME]
sTemp = sOldName
‘List unwanted characters
vaIllegal = [NAME](“.”, “?”, “!”, “*”, “/”, “”, “[", "]“, “‘”)
‘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, [NAME]), sReplacement)
Next i
CleanSheetName = sTemp
End Function
I’m sure I missed something, so let me know when you find it.
Category: Uncategorized  |  Comment (RSS)  |  Trackback
26 Comments
1. [NAME] says:
January 4, 2005 at 1:31 pm
Hi [NAME]
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)
[NAME]
Reply to this comment
2. [NAME] says:
January 4, 2005 at 10:00 pm
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.
Reply to this comment
3. [NAME] says:
January 5, 2005 at 11:08 am
Hi [NAME],
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
[NAME]
Reply to this comment
4. Macutan says:
January 31, 2005 at 11:47 am
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
Reply to this comment
5. [NAME] says:
January 31, 2005 at 3:29 pm
Macutan: The first sub, Workbook_SheetChange, goes in the ThisWorkbook module. The function, CleanSheetName, goes in a standard module (Insert > Module).
Reply to this comment
6. [NAME] says:
February 2, 2005 at 10:30 am
[NAME], 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
[NAME].

Reply to this comment
7. [NAME] says:
February 2, 2005 at 1:39 pm
[NAME] – 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
Reply to this comment
8. [NAME] says:
February 10, 2005 at 3:44 pm
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
Reply to this comment
9. [NAME] says:
February 10, 2005 at 4:08 pm
[NAME]: See here
http://www.dicks-blog.com/archives/2004/06/14/sheet-name/
Reply to this comment
10. [NAME] says:
February 10, 2005 at 4:26 pm
Aha thanks
Reply to this comment
11. [NAME] says:
September 20, 2005 at 4:55 pm
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
Reply to this comment
12. [NAME] says:
September 20, 2005 at 8:29 pm
That’s right [NAME]. Now how the heck did I miss that?
Reply to this comment
13. Amit says:
December 5, 2005 at 5:53 am
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
Reply to this comment
14. Nate says:
December 6, 2005 at 11:28 pm
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?
Reply to this comment
15. [NAME] says:
February 9, 2006 at 5:06 pm
To make the cell value equal to any sheet name, try this:
Put this function in a normal [NAME]:
Function Mysheetname(shindex As [NAME])
Application.Volatile
Mysheetname = Sheets(shindex).Name
End Function
Then put this formula in your worksheet in any cell:
#N/A
(substitute the number in the formula for the sheet location)
Reply to this comment
16. Amber Laws says:
March 5, 2008 at 11:39 am
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 [NAME].
Thanks!
Reply to this comment
17. [NAME] says:
September 3, 2008 at 12:46 am
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
Reply to this comment
18. [NAME] says:
September 16, 2008 at 3:14 am
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.
[NAME]
Reply to this comment
19. [NAME] says:
September 25, 2008 at 7:21 pm
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
Reply to this comment
20. [NAME], Sr. says:
November 10, 2008 at 6:46 pm
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.
Reply to this comment
21. [NAME] says:
December 5, 2008 at 4:37 pm
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
Reply to this comment
22. [NAME] says:
December 16, 2008 at 4:00 pm
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 = “[NAME]”
‘this goes towards the top in declarations
[NAME] 11) As [NAME]
[NAME]) = “Jan”
monthArray(1) = “Feb”
monthArray(2) = “[NAME]”
monthArray(3) = “Apr”
monthArray(4) = “May”
monthArray(5) = “Jun”
monthArray(6) = “Jul”
monthArray(7) = “Aug”
monthArray(8) = “Sep”
[NAME]) = “Oct”
monthArray(10) = “Nov”
monthArray(11) = “Dec”
‘this goes before the assignment but after the formatting of the date
Dim tmpStr() As [NAME]
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 [NAME] (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).
Reply to this comment
23. [NAME] says:
January 25, 2009 at 12:16 pm
Excel sheets are limited to 31 characters, so I changed the last line of the cleaner to
CleanSheetName = Left$(sTemp, 31)
Reply to this comment
24. [NAME] says:
July 1, 2009 at 6:38 am
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 [NAME]) As Boolean
On Error GoTo errHandler
ThisWorkbook.Sheets(1).Name = strSheetName
ValidSheetName = True
ThisWorkbook.Sheets(1).Name = “Control”
Exit Function
On Error GoTo 0
[NAME]:
ValidSheetName = False
End Function
Sub testFunction()
[NAME].Print ValidSheetName(“NameOK”)
[NAME].Print ValidSheetName(“NameNotOk/”)
End Sub
Reply to this comment
25. [NAME] says:
July 1, 2009 at 6:42 am
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 [NAME]) As Boolean
Dim strThisSheet As [NAME]
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
[NAME]:
ValidSheetName = False
End Function
Sub testFunction()
[NAME].Print ValidSheetName(“NameOK”)
[NAME].Print ValidSheetName(“NameNotOk/”)
End Sub
Reply to this comment
26. [NAME] says:
July 1, 2009 at 8:14 am
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 [NAME]) As Boolean
Dim strThisSheet As [NAME]
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
[NAME]:
ValidSheetName = False
End Function