B
Bob G
Is there a formula to name a sheet name from the text in a cell?
Thnaks Bg
Thnaks Bg
Install the app
How to install the app on iOS
Follow along with the video below to see how to install our site as a web app on your home screen.
Note: This feature may not be available in some browsers.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
A
Arvi Laanemets
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
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
G
Gord Dibben
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
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
B
Bob G
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
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
G
Gord Dibben
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
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
B
Bob G
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
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
D
Dave Peterson
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
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
G
Gord Dibben
Bob
Another method if you have many chart sheets.......
Private Sub Chart_Activate()
Dim mycell As Range
Set mycell = Sheets("Jan").Range("$Z$9")
If mycell.Value = "" Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
ActiveSheet.Name = mycell.Value
CleanUp:
Application.EnableEvents = True
End Sub
The event will fire only when you activate the chart sheet, not when Jan!$Z$9
is changed.
This code would go into each chart sheet module with a different Jan!cellref
for each chart sheet.
Gord
Another method if you have many chart sheets.......
Private Sub Chart_Activate()
Dim mycell As Range
Set mycell = Sheets("Jan").Range("$Z$9")
If mycell.Value = "" Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
ActiveSheet.Name = mycell.Value
CleanUp:
Application.EnableEvents = True
End Sub
The event will fire only when you activate the chart sheet, not when Jan!$Z$9
is changed.
This code would go into each chart sheet module with a different Jan!cellref
for each chart sheet.
Gord
B
Bob G
Gord, et al,
Perfect-o!.
Exactly what I was looking for.
Thanks.....once again, for the help you MVPs offer.
You've inspired me to learn more of VBA.
Bg
Perfect-o!.
Exactly what I was looking for.
Thanks.....once again, for the help you MVPs offer.
You've inspired me to learn more of VBA.
Bg
Gord Dibben said:Bob
Another method if you have many chart sheets.......
Private Sub Chart_Activate()
Dim mycell As Range
Set mycell = Sheets("Jan").Range("$Z$9")
If mycell.Value = "" Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
ActiveSheet.Name = mycell.Value
CleanUp:
Application.EnableEvents = True
End Sub
The event will fire only when you activate the chart sheet, not when Jan!$Z$9
is changed.
This code would go into each chart sheet module with a different Jan!cellref
for each chart sheet.
Gord
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 said: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
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
G
Gord Dibben
You've inspired me to learn more of VBA.
Me too<g>
Just curious...............
Which event did you go with? Worksheet_Change in Jan sheet or Chart_Activate
in the chart sheet?
Gord
B
Bg
I pasted your code:
Private Sub Chart_Activate()
Dim mycell As Range
Set mycell = Sheets("Jan").Range("$Z$9")
If mycell.Value = "" Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
ActiveSheet.Name = mycell.Value
CleanUp:
Application.EnableEvents = True
End Sub
into each Chart sheet code and changed the Range cell ref.
Set mycell = Sheets("Jan").Range("$Z$9")
Set mycell = Sheets("Jan").Range("$Z$10")
Set mycell = Sheets("Jan").Range("$Z$11")
Set mycell = Sheets("Jan").Range("$Z$12")
etc.
Thanks
Bg
Private Sub Chart_Activate()
Dim mycell As Range
Set mycell = Sheets("Jan").Range("$Z$9")
If mycell.Value = "" Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
ActiveSheet.Name = mycell.Value
CleanUp:
Application.EnableEvents = True
End Sub
into each Chart sheet code and changed the Range cell ref.
Set mycell = Sheets("Jan").Range("$Z$9")
Set mycell = Sheets("Jan").Range("$Z$10")
Set mycell = Sheets("Jan").Range("$Z$11")
Set mycell = Sheets("Jan").Range("$Z$12")
etc.
Thanks
Bg
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.