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

Hi

No, It isn't. And because a function in Excel can't make any real changes in
workbook, or take any real actions there, it isn't possible by definition.
You have to use a procedure, or some event (p.e. worksheet's Change event).


Arvi Laanemets



"Bob G" wrote in message
...
Is there a formula to name a sheet name from the text in a cell?
Thnaks Bg

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

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

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

The Chart is Chart25. So I changed the code to reflect Chart25 instead of
Chart1 and pasted it into the January sheet code.
The chart name remains the same. No change...am I missing soemthing?

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
Chart25.Name = .Value
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub

Thnaks, again
Bg

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
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

Open excel
Open your workbook
hit alt-f11 (to see the VBE)
hit ctrl-r (to see the project explorer)
Select your project in the project explorer window

Hit the asterisk on the numeric keypad to expand all the branches of that
project.

Under the "Microsoft Excel Objects" branch, you'll see things like:

Sheet1 (Sheet1)
Sheet2 (Jan)
Chart1 (Chart25)

The names in parentheses are the names you can see on the sheet tabs while
you're in excel.

The names in front are the codenames for those sheets.

Gord wants you to change this:
chart25.name = .value

to the codename you see associated with that chart sheet:
chart1.name = .value


Bob G wrote:

The Chart is Chart25. So I changed the code to reflect Chart25 instead of
Chart1 and pasted it into the January sheet code.
The chart name remains the same. No change...am I missing soemthing?

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
Chart25.Name = .Value
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub

Thnaks, again
Bg

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
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





--

Dave Peterson