|
|
|
|
|
|
Excel
|
Show Sheet Name
|
|
Ease of Use
|
Easy
|
Version tested with
|
2000, 2002, 2003
|
Submitted by:
|
Zack Barresse
|
Description:
|
A user-defined formula (UDF) to show the sheet name in a cell.
|
Discussion:
|
As there is no direct route to show the sheet name in a cell through a formula, it can be done much more efficiently using VBA than a native formula. Traditionally one would have to use a combination of the RIGHT/MID and CELL functions to get the sheet name in a cell. This provides an easy and fast way to do this.
|
Code:
|
instructions for use
|
Function ShName(Optional CellRef As Range) As String
Application.Volatile
If CellRef Is Nothing Then
ShName = Application.Caller.Parent.Name
Else
ShName = CellRef.Parent.Name
End If
End Function
|
How to use:
|
- Copy above code.
- In Excel press Alt + F11 to enter the VBE.
- Press Ctrl + R to show the Project Explorer.
- Right-click desired file on left (in bold).
- Choose Insert -> Module.
- Paste code into the right pane.
- Press Alt + Q to close the VBE.
- Save workbook before any other changes.
|
Test the code:
|
- From an existing workbook, save first.
- Select an empty cell.
- Type =ShName(A1), where A1 is any cell reference (optional)
|
Sample File:
|
ShNameEx.zip 7.62KB
|
Approved by mdmackillop
|
This entry has been viewed 448 times.
|
|