Sheet Tab Names

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

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
76,303
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.
 

Forum statistics

Threads
1,141,012
Messages
5,703,729
Members
421,312
Latest member
Mooncake1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top