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?

 

[skyking]

Damon,

I was design my workbook in office 2010 using your sheetname function which I placed in Module 1 and it works great for me. However, if this workbook runs under office 2007, it doesn't show the sheet name. I have to go to the formula bar to apply an enter to made it works for me again.

One more thing, the cell contains the sheetname function is protected.

Can you help?

Sky King

 

[kyf]

Damon's method of displaying cell data into the tab worked on a new workbook (Excel 2010). For some reason, when I applied the same code to the macro of an existing workbook, it does not work. I have saved the workbook as .xlsm document.

Anything specific that need to be done to the existing workbook?

 

[Whang56]

Damon,
This code for nenaming a sheet based on a cell content that you posted-

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

- can you make it work if the cell is on another sheet? I have a master sheet called Weekly Sales that has a range of cells, specifically B8, B10, B12, B14, B16, B18 & B20. I need a worksheet change macro to rename sheet3, sheet4, sheet5, sheet6, sheet7, sheet8 and sheet9 to correspond to the range of cells on the Weekly Report sheet. Is this possible?

 

[steve1033]

Re: Cell Value = Sheet Name... how does the sheet knot which cell to use?

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.

hi there new here . saw this post as it is precicely what i need and wondered .once i place the code in the view code section on the sheet tab . where to from there i am trying to get the date entered and the sheet name on time sheets and the cell C2 has the date in it that i want to use to name the sheet number
thanks in advance
regards
S

 

[atlexcelguy]

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?

For the latter part of your question, it's pretty simple. I use the following formula:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)

Hope this helps.