| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Sheet Tab Names
Is there a formula to name a sheet name from the text in a cell?
Thnaks Bg |
|
#2
|
|||
|
|||
|
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 |
|
#3
|
|||
|
|||
|
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 |
|
#4
|
|||
|
|||
|
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 |
|
#5
|
|||
|
|||
|
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 |
|
#6
|
|||
|
|||
|
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 |
|
#7
|
|||
|
|||
|
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 |
|
#8
|
|||
|
|||
|
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 On Sat, 30 Apr 2005 17:44:43 -0500, Dave Peterson wrote: 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 |
|
#9
|
|||
|
|||
|
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 "Gord Dibben" <gorddibbATshawDOTca wrote in message ... 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 On Sat, 30 Apr 2005 17:44:43 -0500, Dave Peterson wrote: 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 |
|
#10
|
|||
|
|||
|
On Sun, 01 May 2005 17:30:48 GMT, "Bob G" wrote:
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 |
|
#11
|
|||
|
|||
|
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 "Gord Dibben" <gorddibbATshawDOTca wrote in message ... On Sun, 01 May 2005 17:30:48 GMT, "Bob G" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sheet names used in formulas | Excel Discussion (Misc queries) | |||
| I want to print out the sheet tabs (sheet names) | Excel Worksheet Functions | |||
| Locking Sheet names in formulas | Excel Worksheet Functions | |||
| How can I show sheet names in different colours | New Users to Excel | |||
| Naming & renaming a sheet tab | Excel Worksheet Functions | |||
Linear Mode
