Tab Names

[Guest]

Hi,

Is it possible to have Excel populate a cell (for example A1) with the name
of that worksheet tab? This could then be used across the rest of the book.


Many thanks

 

[Bob Phillips]

See http://www.xldynamic.com/source/xld.xlFAQ0002.html

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[smw226 via OfficeKB.com]

Hi Andy,

There may be a much easier way todo this, but you can paste the below into a
module:

Public Function tab_name()
tab_name = ActiveSheet.Name
End Function

then in A1 enter = tab_name()

This will return the tab name into call A1 but if you make a change to the
tabs name, you will have to re-enter the formula as it won't autocalc.

Thanks,

Simon

 

[Guest]

Thank guys both great!

Hi Andy,

There may be a much easier way todo this, but you can paste the below into a
module:

Public Function tab_name()
tab_name = ActiveSheet.Name
End Function

then in A1 enter = tab_name()

This will return the tab name into call A1 but if you make a change to the
tabs name, you will have to re-enter the formula as it won't autocalc.

Thanks,

Simon

 

[Dave Peterson]

I think I'd use:

Option Explicit
Public Function tab_name()
tab_name = Application.Caller.Parent.Name
End Function

If I were using the UDF. Activesheet.name will be the name of the
activesheet--not always the sheet containing the formula.