MrExcel Publishing
Your One Stop for Excel Tips & Solutions

naming worksheet tabs


Posted by rm on December 21, 2001 5:40 PM

2 questions:

1.) Is it possible for a worksheet tab to be linked to a cell value so that the tab will have the same name as the cell value?

2.) If #1 is possible, how is this achieved?

thanks in advance for any help...rm


Posted by Lewis (1) on December 21, 2001 9:10 PM

I'm not sure if this is what you want but it is possible to name a cell, so that you can hyperlink to it as a bookmark. Obviously you can give the cell the same name name as the worksheet.

To name the cell
Select the cell you wish to name.
In the top left hand corner is a box with the cell address in it.
Click on this and you can then edit it.
type in the name you want and press enter.
This names the cell.

Lewis

Posted by Mark O'Brien on December 21, 2001 9:17 PM

Sort of. You would have to use VBA.

This code will do the job, just put it on the workbook object:


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim MySh As Worksheet

For Each MySh In Worksheets
If MySh.Name = Sh.Range("A1").Value _
And Not MySh Is Sh Then
Application.Undo
MsgBox "Cannot Have Two Sheets with the same name, please rename.", vbCritical
Exit Sub
End If
Next

If Not Sh.Name = Sh.Range("A1").Value _
And Not Sh.Range("A1").Value = "" Then
Sh.Name = Sh.Range("A1").Value
End If
End Sub

This code assumes that the worksheet name will be in cell A1, you can change it to whatever you want.

Posted by Mark O'Brien on December 21, 2001 9:18 PM

PS

The code doesn't check if the name contains invalid characters. Like "\" for example. For Each MySh In Worksheets If MySh.Name = Sh.Range("A1").Value _ And Not MySh Is Sh Then Application.Undo MsgBox "Cannot Have Two Sheets with the same name, please rename.", vbCritical Exit Sub End If Next If Not Sh.Name = Sh.Range("A1").Value _ And Not Sh.Range("A1").Value = "" Then Sh.Name = Sh.Range("A1").Value End If

Posted by rm on December 22, 2001 11:46 PM

Happy Holidays...

...much mahalo (thank you) for the help and have a happy holiday season!!!