Sheet name to cell


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
 
Ad

Advertisements

I

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.
 
I

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
 
W

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
 
Ad

Advertisements

J

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top