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
|