Cell Value = Sheet Name

njws

Board Regular
Joined
Nov 5, 2003
Messages
50
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?
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

AlohaJulio

Board Regular
Joined
Feb 10, 2004
Messages
188
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

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
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.
 

RGH

Board Regular
Joined
Dec 17, 2003
Messages
71

ADVERTISEMENT

try

=SHEETNAME()
 

hesduffy

New Member
Joined
Jun 30, 2011
Messages
1

ADVERTISEMENT

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

New Member
Joined
Aug 28, 2011
Messages
8
Damon,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p> </o:p>
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:p> </o:p>
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:p> </o:p>
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:p> </o:p>
Ashton
 

AliveByDesign

New Member
Joined
Aug 28, 2011
Messages
8
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,861
Messages
5,598,493
Members
414,243
Latest member
Shockpulsar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top