Sheet name to cell

  • Thread starter Thread starter jan120253
  • Start date Start date
J

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
 
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,
Click to expand...

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.
 
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,
Click to expand...

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
Click to expand...
Click to expand...
 
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
Click to expand...

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
Click to expand...

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

Jan
 
Back
Top