Cell Value = Sheet Name

[njws]

Is it possible to have a cell dictate what the sheet name is? Similarly, is it possible to have a cell value return the sheet name?

 

[AlohaJulio]

I asked for help recently to create a macro that takes the text in cell A1 on every worksheet and renames all the tabs to that text. It may not be exactly what you are looking for, but it might get you started. Here's the code:

Sub RenameFromA1()
Dim Msg As String, i As Integer
For i = 1 To Sheets.Count
If Sheets(i).Range("A1").Value = "" Then
Msg = "Sheet " & i & "(" & Sheets(i).Name & ") has no value in A1. Fix sheet, then rerun."
MsgBox Msg, vbExclamation
Exit Sub
Else
On Error GoTo ErrSheetName
Sheets(i).Name = Sheets(i).Range("A1").Value
On Error GoTo 0
End If
Next i
Exit Sub
ErrSheetName: Msg = "Sheet " & i & "(" & Sheets(i).Name & ") could not be renamed. Check if name already used."
MsgBox Msg, vbExclamation

End Sub

 

[Damon Ostrander]

Hi njws,

The answer to both questions is yes. For the first question, the following user-defined function (UDF) will yield the worksheet name. This function can be placed in a standard macro module.

Function SheetName() As String
' Returns the name of the worksheet from which the function is called
SheetName = Application.Caller.Parent.Name
Application.Volatile
End Function

To install this code, use keyboard Alt-TMV to go to the Visual Basic Editor, Alt-IM to insert a new macro module, and paste the code into the Code Pane.


The second question requires an event macro. As an example the following macro will rename the sheet to whatever string is entered into cell B4:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$4" Then
Name = [b4].Text
End If
End Sub

Because it is an event macro it must be placed in the event code module of the worksheet. To do this, right-click on the worksheet's tab, select View Code, and paste the code into the Code Pane.

 

[Damon Ostrander]

njws,

Oops. Please be aware that I answered the questions in reverse order.

Damon

 

[RGH]

try

=SHEETNAME()

 

[njws]

Thanks all!

 

[hesduffy]

Hey I'm having trouble with this function. I have followed the instructions carefully - and at one time it worked... but I reopened the file and it seems that now I reference the function. It shows up in my fx bar and I hit "tab" to input the correct syntax but it will only display the "#NAME?" error. Help?

 

[AliveByDesign]

Damon,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Your code for renaming a worksheet tab via referencing a cell works pretty well, but I’m still having trouble getting the tab to rename itself automatically as opposed to me manually having to highlight the cell function in the function bar and then pressing ENTER. Allow me to explain further.
<o> </o>
I have a single workbook with two worksheets; worksheet A and worksheet B. What I’m trying to do is have cell B1 in worksheet B rename its tab with the content from cell B1. However, I have cell B1 functioned to populate the text data from cell A1 of worksheet A. In essence, I have =A!A1 as the function inside cell B1 of worksheet B. When I enter in, let’s say XXXXX in cell A1 of worksheet A, it populates cell B1 in worksheet B, but worksheet B’s tab does not automatically rename itself from the functioned result in cell B1. For some reason, the only way I can get the tab to rename “automatically” is go up to the function bar, highlight the function, and then press ENTER.
<o> </o>
I was wondering if you may be able to help me out on resolving this. Whether it’s an adjustment to the VBA code you mentioned or maybe a setting within Excel 2003, I would very much appreciate any assistance you can provide. Thank you!
<o> </o>
Ashton

 

[AliveByDesign]

Hey Everyone,

If you're having the same problem I was, Damon's new code took care of the problem. Simply add the code below to you VB Editors code window, save your file, and your tab will rename itself automatically if the cell the tab is referencing contains the result of a function or formula. Kudos goes out to Damon!

Private Sub Worksheet_Calculate()
If Range("B1").Text <> Me.Name Then Me.Name = Range("B1").Text
End Sub

 

[brettdj]

This code would benefit from two additions to handle errors
- invalid sheet names
- duplicated sheet names

This thread contains a solution to a similar issue with handling for both issues (a regex for the former, dictionary for the later)
http://www.mrexcel.com/forum/showthread.php?t=575065

Cheers

Dave