A
Ablang
Q: I regularly produce Excel documents in which a cell on each
worksheet contains the tab name of that worksheet. I have to type the
tab name into the cell after I set the name on the tab. With small
spreadsheets this isn't a problem, but my spreadsheets have more and
more tabs lately. I tried creating a tabName() function, returning the
value ActiveCell.Worksheet.Name. But if I recalculate the spreadsheet,
all of the cells get the name of the tab that is active at that time.
How can I get the worksheet tab name into a cell on that worksheet? -
Steve Durette
A: You were headed in the right direction, but you took a wrong turn.
There's only one active cell at a given time. When you force a recalc,
every instance of your function on every sheet gets its value from the
tab containing that single active cell. What you want instead is the
name of the worksheet where the function is being called.
There's a handy-dandy property that's not as well known as it should
be: Application.Caller. When a function is called from a worksheet
cell, the Caller property's value is a Range object representing that
cell. The cell range's Parent property is the worksheet where the cell
resides. So this expression returns the name of the worksheet
containing that cell: Application.Caller.Parent.Name. But if you
change the name of the worksheet tab, you'll want the cell to update
as well. To make that happen, set Application.Volatile to "True" in
your function, meaning it should be recalculated automatically, like a
formula.
Of course, you'll want this function to be available for all of your
worksheets, so you need to create it in your Personal Macro Workbook.
Unless you've done this before, you don't have a Personal Macro
Workbook yet; here's an easy way to make Excel create it for you.
Start to record a macro and choose Personal Macro Workbook from the
drop-down list labeled Store macro in. Click OK and immediately stop
macro recording. Now you're ready to build a macro.
Select Tools | Macro | VBA Editor from the menu. In the tree structure
at left, you should see a top-level item called
VBAProject(Personal.xls). Double-click it to open, double-click the
Modules branch below it, and double-click Module1 below that. You'll
see the empty macro that you just created. Delete it. Then type or
copy/paste the following simple function into the module:
Function tabName() As String
Application.Volatile True
tabName = Application.Caller.Parent.Name
End Function
Press Alt-Q to close the VBA Editor and return to Excel. Now you can
use =tabName() in any cell that needs to display the current tab name.
worksheet contains the tab name of that worksheet. I have to type the
tab name into the cell after I set the name on the tab. With small
spreadsheets this isn't a problem, but my spreadsheets have more and
more tabs lately. I tried creating a tabName() function, returning the
value ActiveCell.Worksheet.Name. But if I recalculate the spreadsheet,
all of the cells get the name of the tab that is active at that time.
How can I get the worksheet tab name into a cell on that worksheet? -
Steve Durette
A: You were headed in the right direction, but you took a wrong turn.
There's only one active cell at a given time. When you force a recalc,
every instance of your function on every sheet gets its value from the
tab containing that single active cell. What you want instead is the
name of the worksheet where the function is being called.
There's a handy-dandy property that's not as well known as it should
be: Application.Caller. When a function is called from a worksheet
cell, the Caller property's value is a Range object representing that
cell. The cell range's Parent property is the worksheet where the cell
resides. So this expression returns the name of the worksheet
containing that cell: Application.Caller.Parent.Name. But if you
change the name of the worksheet tab, you'll want the cell to update
as well. To make that happen, set Application.Volatile to "True" in
your function, meaning it should be recalculated automatically, like a
formula.
Of course, you'll want this function to be available for all of your
worksheets, so you need to create it in your Personal Macro Workbook.
Unless you've done this before, you don't have a Personal Macro
Workbook yet; here's an easy way to make Excel create it for you.
Start to record a macro and choose Personal Macro Workbook from the
drop-down list labeled Store macro in. Click OK and immediately stop
macro recording. Now you're ready to build a macro.
Select Tools | Macro | VBA Editor from the menu. In the tree structure
at left, you should see a top-level item called
VBAProject(Personal.xls). Double-click it to open, double-click the
Modules branch below it, and double-click Module1 below that. You'll
see the empty macro that you just created. Delete it. Then type or
copy/paste the following simple function into the module:
Function tabName() As String
Application.Volatile True
tabName = Application.Caller.Parent.Name
End Function
Press Alt-Q to close the VBA Editor and return to Excel. Now you can
use =tabName() in any cell that needs to display the current tab name.