Name of active sheet

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,270
Office Version
365, 2016, 2007
Platform
Windows
An Internet search turned up this expression for obtaining the name of the active sheet.
Code:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
It seems to work, but is there really nothing simpler and more straightforward?
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,746
Office Version
2010
Platform
Windows
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

That returns the name of the sheet containing A1, which may (well) not be the active sheet.
 

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,270
Office Version
365, 2016, 2007
Platform
Windows
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

That returns the name of the sheet containing A1, which may (well) not be the active sheet.
I wondered about that. But if the formula is in a cell in Sheet1, wouldn't the "A1" refer to that sheet?

How about this solution?

Code:
Public Function ThisSheetName()
ThisSheetName = Application.Caller.Worksheet.Name
End Function
Any problems with that?

PS: I thought I already posted this in response to Logit, but it doesn't seem to be here.
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,746
Office Version
2010
Platform
Windows
On Sheet1:
A​
B​
1​
Sheet1A1: =MID(CELL("filename", A1), FIND("]", CELL("filename", A1)) + 1, 31)
2​
Sheet1A2: =MID(CELL("filename"), FIND("]", CELL("filename")) + 1, 31)

On Sheet2:
A​
B​
1​
Sheet1A1: =Sheet1!A2
2​
Sheet2A2: =Sheet1!A2

Select Sheet1 and press F9; what do you see?

Select Sheet2; what do you see?

Then press F9; what happens?

Absent a sheet reference, the formula returns the activesheet's name, but doesn't update until calculation occurs.
 
Last edited:

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,746
Office Version
2010
Platform
Windows
Sorry, that got messed up.

A​
B​
1​
Sheet1A1: =MID(CELL("filename", A1), FIND("]", CELL("filename", A1)) + 1, 31)
2​
Sheet1A2: =MID(CELL("filename"), FIND("]", CELL("filename")) + 1, 31)

Sheet2:

A​
B​
1​
Sheet1A1: =Sheet1!A1
2​
Sheet2A2: =Sheet1!A2

Select Sheet1 and now it shows

Sheet1
Sheet2

Press F9 and it shows

Sheet1
Sheet1

Absent a cell reference, the formula returns the active sheet's name, but doesn't update until calculation occurs.
 

BlueHornet

Active Member
Joined
Apr 13, 2012
Messages
338
Very nice, shg.

For a long time I've used AutoHotKey replacement text values to return the values:

ThisWorksheet enters =RIGHT( CELL( "filename", $A$1), LEN( CELL( "filename", $A$1)) - FIND( "]", CELL( "filename", $A$1)))
ThisWorkbook enters =MID( CELL( "filename", $A$1), FIND( "[", CELL( "filename", $A$1)) + 1, FIND( "]", CELL( "filename", $A$1)) - FIND( "[", CELL( "filename", $A$1)) -1)
ThisPath enters =LEFT( CELL( "filename", $A$1), FIND( "[", CELL( "filename", $A$1)) -1 )
ThisCanonicalFile enters =LEFT( CELL( "filename", $A$1), FIND( "[", CELL( "filename", $A$1)) -1 ) & MID( CELL( "filename", $A$1), FIND( "[", CELL( "filename", $A$1)) + 1, FIND( "]", CELL( "filename", $A$1)) - FIND( "[", CELL( "filename", $A$1)) -1)

(Something similar could be done within Excel in an AutoCorrect, I think.) These are pretty helpful when there's a need to work with INDIRECTs, and especially when worksheet names, file names and/or locations might change from time to time.

Your method for simplifying This Worksheet is neater and shorter than mine, and workable at least until Microsoft reissues Excel with longer Worksheet names than 31 characters.
 
Last edited:

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,137
.
If you want to display the sheet name automatically, you can paste this in the Sheet Level Module :

Code:
Option Explicit


Private Sub Worksheet_Activate()
    Range("A1").Value = ActiveSheet.Name
End Sub
Drawback to above is that the macro needs to be pasted into every sheet module.



If you want the sheet name to appear when you click a command button, you can paste this into a regular module and connect the button
to this macro :

Code:
Sub whatName()
Range("A1").Value = ActiveSheet.Name
End Sub
Drawback to the above is each sheet requires a command button.




You can paste this into the ThisWorkbook Module :

Code:
Option Explicit


Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    
    Range("A1").Value = ActiveSheet.Name
       
End Sub
Although this macro only requires one pasting into the ThisWorkbook Module the drawback to the above is your workbook will auto open
to Sheet 1 and the sheet name will not display in Cell A1 until you select a different sheet and return to Sheet 1.
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,746
Office Version
2010
Platform
Windows
Code:
Function SheetName(Optional cell As Range) As String
  If cell Is Nothing Then
    SheetName = Application.Caller.Worksheet.Name
  Else
    SheetName = cell.Worksheet.Name
  End If
End Function
 

Watch MrExcel Video

Forum statistics

Threads
1,096,459
Messages
5,450,577
Members
405,619
Latest member
mjh237

This Week's Hot Topics

Top