Sheet Tab Names

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

patrickmuldoon99

Active Member
Joined
Jun 27, 2006
Messages
345
I'm not entirely sure what you mean

Do you mean, could we write a formula that returned the active sheets name? Well yes, you could write a UDF that returns activesheet.name

Or do you mean VBA? When you could also use activesheet.name as the activesheet will be the one with that cell of interest on it.
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi Mark

Try the following:

Code:
=REPLACE(CELL("filename",$A$1),1,FIND("]",CELL("filename",$A$1)),"")

Best regards

Richard
 

MarkAndrews

Well-known Member
Joined
May 2, 2006
Messages
1,963
Patrick - What i would like to do is type text into a cell, then link the sheet tab name to this cell.......... is it possible?

Richard - Is that a formula on the sheet?
 

patrickmuldoon99

Active Member
Joined
Jun 27, 2006
Messages
345

ADVERTISEMENT

Hmmm I guess you could do that with some sort of a selection change event. For example:

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
ActiveSheet.Name = [a1]
End Sub

Of course, you would have to be carefulu that a1 did not contain any characters that are illegal in a sheet name.... maybe some sort of checking of the value before it is assigned to activesheet.name?
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
I would use a slightly different event (no need to rename sheet on every selection change):

Code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error Resume Next
If Not Intersect(Target, Range("A1")) Is Nothing Then ActiveSheet.Name = Range("A1").Value
End Sub

Best regards

Richard
 

MarkAndrews

Well-known Member
Joined
May 2, 2006
Messages
1,963

ADVERTISEMENT

So i couldn't have

=$B$1&" - "&$D$1 (Result : April - June)

B1 = April
D1 = June

??
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
If the referenced values are likely to change (ie B1 and D1) then you need a calculation event:

Code:
Private Sub Worksheet_Calculate()
On Error Resume Next
Sheets(ThisWorksheetName).Name = Range("A1").Value
End Sub

This will work, but suffers from similar inefficiencies as the SelectionChange event does - this could be avoided with some other coding, but depending on your circumstances, I think that's overkill.

Richard
 

MarkAndrews

Well-known Member
Joined
May 2, 2006
Messages
1,963
If the referenced values are likely to change (ie B1 and D1) then you need a calculation event:

Code:
Private Sub Worksheet_Calculate()
On Error Resume Next
Sheets(ThisWorksheetName).Name = Range("A1").Value
End Sub

This will work, but suffers from similar inefficiencies as the SelectionChange event does - this could be avoided with some other coding, but depending on your circumstances, I think that's overkill.

Richard

Thanks Richard - I'll revisit this post when i need to take this forward

I may come back to you if thats OK

Mark
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,912
Office Version
  1. 365
Platform
  1. Windows
Mark

What exactly are you looking for?

If you had April in B1 and June in D1 then the result of the formula =$B$1&" - "&$D$1 would be April - June.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,811
Messages
5,544,451
Members
410,612
Latest member
MrACED
Top