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 '** Will change if Sheet name is altered later Application.Volatile '** Checking to see if a cell reference was added If CellRef Is Nothing Then '** If not, go another route ShName = Application.Caller.Parent.Name Else '** If so, use the cell reference ShName = CellRef.Parent.Name End If End Function

How to use:

  1. Copy above code.
  2. In Excel press Alt + F11 to enter the VBE.
  3. Press Ctrl + R to show the Project Explorer.
  4. Right-click desired file on left (in bold).
  5. Choose Insert -> Module.
  6. Paste code into the right pane.
  7. Press Alt + Q to close the VBE.
  8. Save workbook before any other changes.
 

Test the code:

  1. From an existing workbook, save first.
  2. Select an empty cell.
  3. 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.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express