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:
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:
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.
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.
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
4 January 2005, 1:31 pmPaul 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.
4 January 2005, 10:00 pmCharlie:
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
5 January 2005, 11:08 amMacutan:
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
31 January 2005, 11:47 amDick:
Macutan: The first sub, Workbook_SheetChange, goes in the ThisWorkbook module. The function, CleanSheetName, goes in a standard module (Insert > Module).
31 January 2005, 3:29 pmJamie 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.
--
2 February 2005, 10:30 amDick:
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
2 February 2005, 1:39 pmRobert 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
10 February 2005, 3:44 pmDick:
Robert: See here
http://www.dicks-blog.com/archives/2004/06/14/sheet-name/
10 February 2005, 4:08 pmRobert Linggoputro:
Aha thanks
10 February 2005, 4:26 pmJulian:
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
20 September 2005, 4:55 pmDick Kusleika:
That's right Julian. Now how the heck did I miss that?
20 September 2005, 8:29 pmAmit:
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
5 December 2005, 5:53 amNate:
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?
6 December 2005, 11:28 pmJeremy:
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)
9 February 2006, 5:06 pmAmber 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!
5 March 2008, 11:39 amKelly:
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.
3 September 2008, 12:46 amI 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
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.
16 September 2008, 3:14 amJason
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
25 September 2008, 7:21 pmRochelle 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.
10 November 2008, 6:46 pm