Sheet name to cell

[jan120253]

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

 

[isabelle]

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, (e-mail address removed) 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.

 

[isabelle]

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, (e-mail address removed) 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

 

[witek]

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

add any cell from current worksheet as argument
If you find a cell which changes frequently you event do not need make
this function as volatile.

Function Arknavn(rng as range)
Application.Volatile
Arknavn = rng.worksheet.Name
End Function

 

[jan120253]

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