Sheet name to cell
 

I use this UDF to transfer a sheetname to a cell:

Function Arknavn()
Application.Volatile
Arknavn = ActiveSheet.Name
End Function

Unfortunately as it use ActiveSheet it doesn't do what I want it to, if I have to use it in more that one sheet, and changes name of a sheet as it always display the name of the active sheet, that is, the sheet who's name I'm changing.

I want it to always show the name of the sheet, in wich it is used, even if its used in more sheets than one.

Can this be done, and if so how?

Jan

Sheet name to cell
 

hi Jan,

this formula work only if the file is saved.

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,9^9)

isabelle


Le 2013-01-01 18:27, a écrit :
I use this UDF to transfer a sheetname to a cell:

Function Arknavn()
Application.Volatile
Arknavn = ActiveSheet.Name
End Function

Unfortunately as it use ActiveSheet it doesn't do what I want it to, if I have to use it in more that one sheet,

and changes name of a sheet as it always display the name of the active
sheet, that is, the sheet who's name I'm changing.

I want it to always show the name of the sheet, in wich it is used, even if its used in more sheets than one.

Can this be done, and if so how?

Jan

Sheet name to cell
 

another way is to place the following macro in Thisworkbook

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Range("A1") = Sh.Name
End Sub

isabelle


Le 2013-01-01 19:31, isabelle a écrit :
hi Jan,

this formula work only if the file is saved.

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,9^9)

isabelle


Le 2013-01-01 18:27, a écrit :
I use this UDF to transfer a sheetname to a cell:

Function Arknavn()
Application.Volatile
Arknavn = ActiveSheet.Name
End Function

Unfortunately as it use ActiveSheet it doesn't do what I want it to,
if I have to use it in more that one sheet,

and changes name of a sheet as it always display the name of the active
sheet, that is, the sheet who's name I'm changing.

I want it to always show the name of the sheet, in wich it is used,
even if its used in more sheets than one.

Can this be done, and if so how?

Jan

Sheet name to cell
 

Sheet name to cell
 

Function Arknavn(rng as range)

Application.Volatile

Arknavn = rng.worksheet.Name

End Function

Thank you all. All you solutions worked, but I chose witek's.

Jan