Bob

Assuming the chart sheet in question is Chart1(sheetname)which you can find in
VBE workbook objects, paste this into the Jan sheet module, not into the chart
sheet module.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("$Z$9")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
If .Value < "" Then
Chart1.Name = .Value
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub


Gord


On Sat, 30 Apr 2005 17:36:29 GMT, "Bob G" wrote:

That worked 100%.
However, not knowing what I should, it should have mentioned it is a Chart
sheet tab, and want to referencee a cell from another worksheet..
I tried Jan!$Z$9 in the code you supplied with no results.

Private Sub Worksheet_Change(ByVal Target As Range)
'autoname the worksheet Tab from value in Jan!$Z$9
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("Jan!$Z$9")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
If .Value < "" Then
Me.Name = .Value
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub

Any work around here. Thanks again.
Bg

"Gord Dibben" <gorddibbATshawDOTca wrote in message
.. .
Not a formula, but vba CODE.

From Bob Phillips..........

Private Sub Worksheet_Change(ByVal Target As Range)
'autoname the worksheet Tab from value in A5
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("A5")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
If .Value < "" Then
Me.Name = .Value
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub

Alternative on a button or shortcut key.

Sub SheetName()
ActiveSheet.Name = Range("A5")
End Sub


Gord Dibben Excel MVP

On Sat, 30 Apr 2005 16:40:46 GMT, "Bob G" wrote:

Is there a formula to name a sheet name from the text in a cell?
Thnaks Bg