Display Sheetname in a cell

[Clif McIrvin]

I'd like to display a worksheet name "with the body" instead of "in the
margins" (ie, using Header / Footer definitions).

Is there a built-in mechanism for picking up the worksheet name in a
formula?

I tried putting

Function GetSheetName() As String
GetSheetName = ActiveSheet.Name
End Function

in a code module and " = GetSheetName() " in a cell; but quickly
discovered that <F9> doesn't cause the user defined function to
re-evaluate.

Suggestions?

 

[T. Valko]

Try this:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

You can put that in *any* cell on the sheet. The file must have been saved
at least once and given a name.

 

[Shailesh Shah]

Use application.volatile


Function GetSheetName() As String
Application.Volatile
GetSheetName = ActiveSheet.Name
End Function



Regards,
Shailesh Shah
http://in.geocities.com/shahshaileshs/
If You Can't Excel with Talent, Triumph with Effort.

Free Addins Office Menu-2003 for Office-2007
http://in.geocities.com/shahshaileshs/menuaddins





Use Free Accounting Software "Busywin UL 3.5 "as per Principle of Book
Keeping having vat enabled invoice printing and vat computation with
inventory tracking by Busy Infotech from below page.
http://www.busy.in/index.php?p=fas

 

[Clif McIrvin]

Thanks! I'd seen the cell function help, but didn't realize that the
sheetname was included as part of filename.

Just what I needed.

--
Clif

Try this:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

You can put that in *any* cell on the sheet. The file must have been
saved at least once and given a name.

 

[Clif McIrvin]

Thank you, Shailesh.

This time I'll probably stay with the native CELL worksheet function
because that doesn't require a code module if someone happens to copy
the worksheet to a different book <grin>.

However, I'll try to remember Application.Volatile for future reference!

Thanks again.

--
Clif

Use application.volatile


Function GetSheetName() As String
Application.Volatile
GetSheetName = ActiveSheet.Name
End Function



Regards,
Shailesh Shah
http://in.geocities.com/shahshaileshs/
If You Can't Excel with Talent, Triumph with Effort.

Free Addins Office Menu-2003 for Office-2007
http://in.geocities.com/shahshaileshs/menuaddins





Use Free Accounting Software "Busywin UL 3.5 "as per Principle of Book
Keeping having vat enabled invoice printing and vat computation with
inventory tracking by Busy Infotech from below page.
http://www.busy.in/index.php?p=fas

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

Thanks! I'd seen the cell function help, but didn't realize that the
sheetname was included as part of filename.

Just what I needed.

--
Clif

Try this:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

You can put that in *any* cell on the sheet. The file must have been
saved at least once and given a name.

--
Biff
Microsoft Excel MVP

I'd like to display a worksheet name "with the body" instead of "in the
margins" (ie, using Header / Footer definitions).

Is there a built-in mechanism for picking up the worksheet name in a
formula?

I tried putting

Function GetSheetName() As String
GetSheetName = ActiveSheet.Name
End Function

in a code module and " = GetSheetName() " in a cell; but quickly
discovered that <F9> doesn't cause the user defined function to
re-evaluate.

Suggestions?

 

[Dave Peterson]

But using the activesheet's name could cause trouble. No matter which sheet
held the formula, it would always return the name of the activesheet when excel
recalculated.

Option Explicit
Function GetSheetName() As String
Application.Volatile
GetSheetName = Application.Caller.Parent.Name
End Function

application.caller is the cell that holds the formula.

So application.caller.parent is the worksheet that holds that cell.

 

[Clif McIrvin]

But using the activesheet's name could cause trouble. No matter which
sheet
held the formula, it would always return the name of the activesheet
when excel
recalculated.

Very crucial distinction. I can see that .Caller is another useful
property worth being familiar with!

Thanks!