Returing Sheet name in a cell

[THETORBY5]

What is the formula to return a sheet name into a cell. The cell that would display the sheet name is not in the same sheet.

 

[KniteMare]

sheets("YourOtherSheet").Range("TheRangeOr CellOnThatSheet") = ActiveSheet.Name

For example

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Sheets("MySheet").Range("A1") = ActiveSheet.Name
End Sub

My sheet 1 is renamed MyMan
My Sheet 2 is renamed MySheet

The above puts MyMan in cell A1 of the sheet named MySheet when ever you click anywhere on the sheet named MyMan

Yours in EXCELent Frustration

KniteMare
This message was edited by KniteMare on 2002-08-23 09:01

 

[Smiffy]

If the sheet name you need to return is some other sheet than where the formula is located, how are you going to specify what sheet?

 

[Aladin Akyurek]

On 2002-08-23 08:57, Smiffy wrote:
If the sheet name you need to return is some other sheet than where the formula is located, how are you going to specify what sheet?

Good question...

The following perhaps:

=SHEETNAME(2)

which produces the sheetname as given by the user referring to the internal index that Excel maintains.

The function is part of the morefunc add-in, which is available from:

http://longre.free.fr/english/index.html

 

[KniteMare]

Aladin:
That is interesting, and the AddIn reference explains why I could not find it as a formula, thus my prior submitted response as a VBA solution.

Learn something new every day!

Yours in EXCELent Frustration

KniteMare

 

[THETORBY5]

Thank you for your help, it worked.

How about the reverse. How do I make a sheetname equal a cell name within that sheet. Once that is accomplished how do I correct all my previous VBA code in the workbook referencing; for example a sheet named projections when the cell that determines the sheetname changes from "projections" to "actuals".

 

[KniteMare]

THETORBY5
Your code, i am assuming here, references the sheets by name not by sheet number. You need to change these references to variibles

something like this

dim shtName as string
shtName = Sheets(1).Name'or what ever the sheet reference number is

Sheets(shtName)' and what ever you were doing with the sheet name to follow here


To do the reverse:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Sheets(2).Name = "MySheet"
End Sub



Yours in EXCELent Frustration

KniteMare



_________________
Of course I can, and it will take only 900 hours of programming time to do it.
This message was edited by KniteMare on 2002-08-23 12:05

 

[THETORBY5]

Thank you again.