Name of active sheet

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,183
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?
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,719
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,183
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,719
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,719
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
336
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
2,924
.
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,719
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
 

Forum statistics

Threads
1,082,612
Messages
5,366,606
Members
400,906
Latest member
incanus

Some videos you may like

This Week's Hot Topics

Top