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

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

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

wrote:
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

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