Sheet Tab Names

[MarkAndrews]

Is it possible to get the sheet tab name from a cell ?

TIA

 

[patrickmuldoon99]

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]

Hi Mark

Try the following:

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

Best regards

Richard

 

[MarkAndrews]

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]

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

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]

I would use a slightly different event (no need to rename sheet on every selection change):

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]

So i couldn't have

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

B1 = April
D1 = June

??

 

[Richard Schollar]

If the referenced values are likely to change (ie B1 and D1) then you need a calculation event:

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]

If the referenced values are likely to change (ie B1 and D1) then you need a calculation event:

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]

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.