Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
automatically change worksheet tab name
I have 16 sheets that are numbered 1 thru 16 on the tab. I also have a
17th sheet I have renamed "Data" (ignore quotes). I would like to type a name, (i.e., wonder) into cell a1 of the Data sheet and have that automatically change the name of sheet number 1 to "wonder". Then I would type another name (i.e., Hanger) into on the data sheet into cell a2 and that would change sheet number 2 to Hanger. I would continue to do this until all sheets are now changed to a name rather than a numer. Can this be accomplished by a formula? Thank you, Wally |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
automatically change worksheet tab name
This will need to be done using VBA.
Press Alt+F11 to enter the VB Editor. On the left, you should see your workbook listed as "VBAProject(YourWorkbook)". If there is a plus to the left of it, click it to make it a minus which expands the list as well. Do the same thing for "Microsoft Excel Objects". Double-click on ThisWorkbook. Paste the following code in the right-hand pane. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Stop If ActiveSheet.Name = "Data" And Sheets(Target.Row).Name < "Data" _ Then If Target.Column = 1 And Target.Value < "" Then Sheets(Target.Row).Name = Target.Value Else End If End Sub HTH, Paul -- "wally" wrote in message ... I have 16 sheets that are numbered 1 thru 16 on the tab. I also have a 17th sheet I have renamed "Data" (ignore quotes). I would like to type a name, (i.e., wonder) into cell a1 of the Data sheet and have that automatically change the name of sheet number 1 to "wonder". Then I would type another name (i.e., Hanger) into on the data sheet into cell a2 and that would change sheet number 2 to Hanger. I would continue to do this until all sheets are now changed to a name rather than a numer. Can this be accomplished by a formula? Thank you, Wally |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
automatically change worksheet tab name
On Jun 26, 4:12*pm, "PCLIVE" wrote:
This will need to be done using VBA. Press Alt+F11 to enter the VB Editor. On the left, you should see your workbook listed as "VBAProject(YourWorkbook)". If there is a plus to the left of it, click it to make it a minus which expands the list as well. Do the same thing for "Microsoft Excel Objects". Double-click on ThisWorkbook. Paste the following code in the right-hand pane. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Stop If ActiveSheet.Name = "Data" And Sheets(Target.Row).Name < "Data" _ * * Then * * * * If Target.Column = 1 And Target.Value < "" Then Sheets(Target.Row).Name = Target.Value * * Else End If End Sub HTH, Paul -- "wally" wrote in message ... I have 16 sheets that are numbered 1 thru 16 on the tab. I also have a 17th sheet I have renamed "Data" (ignore quotes). I would like to type a name, (i.e., wonder) into cell a1 of the Data sheet and have that automatically change the name of sheet number 1 to "wonder". Then I would type another name (i.e., Hanger) into on the data sheet into cell a2 and that would change sheet number 2 to Hanger. I would continue to do this until all sheets are now changed to a name rather than a numer. Can this be accomplished by a formula? Thank you, Wally- Hide quoted text - - Show quoted text - Hi Paul, Followed your instructions but when I type in a name in cell A1 on sheet Data, nothing happens. I go to VBA and the first line (Private Sub Workbook, etc) is highlighted in yellow. Wally |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
automatically change worksheet tab name
Oops. Sorry about that.
Remove "Stop" from the beginning of the code. That was there for testing purposes. -- "wally" wrote in message ... On Jun 26, 4:12 pm, "PCLIVE" wrote: This will need to be done using VBA. Press Alt+F11 to enter the VB Editor. On the left, you should see your workbook listed as "VBAProject(YourWorkbook)". If there is a plus to the left of it, click it to make it a minus which expands the list as well. Do the same thing for "Microsoft Excel Objects". Double-click on ThisWorkbook. Paste the following code in the right-hand pane. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Stop If ActiveSheet.Name = "Data" And Sheets(Target.Row).Name < "Data" _ Then If Target.Column = 1 And Target.Value < "" Then Sheets(Target.Row).Name = Target.Value Else End If End Sub HTH, Paul -- "wally" wrote in message ... I have 16 sheets that are numbered 1 thru 16 on the tab. I also have a 17th sheet I have renamed "Data" (ignore quotes). I would like to type a name, (i.e., wonder) into cell a1 of the Data sheet and have that automatically change the name of sheet number 1 to "wonder". Then I would type another name (i.e., Hanger) into on the data sheet into cell a2 and that would change sheet number 2 to Hanger. I would continue to do this until all sheets are now changed to a name rather than a numer. Can this be accomplished by a formula? Thank you, Wally- Hide quoted text - - Show quoted text - Hi Paul, Followed your instructions but when I type in a name in cell A1 on sheet Data, nothing happens. I go to VBA and the first line (Private Sub Workbook, etc) is highlighted in yellow. Wally |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
automatically change worksheet tab name
I removed stop and the same thing happens.
On Jun 26, 5:03*pm, "PCLIVE" wrote: Oops. *Sorry about that. Remove "Stop" from the beginning of the code. *That was there for testing purposes. -- "wally" wrote in message ... On Jun 26, 4:12 pm, "PCLIVE" wrote: This will need to be done using VBA. Press Alt+F11 to enter the VB Editor. On the left, you should see your workbook listed as "VBAProject(YourWorkbook)". If there is a plus to the left of it, click it to make it a minus which expands the list as well. Do the same thing for "Microsoft Excel Objects". Double-click on ThisWorkbook. Paste the following code in the right-hand pane. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Stop If ActiveSheet.Name = "Data" And Sheets(Target.Row).Name < "Data" _ Then If Target.Column = 1 And Target.Value < "" Then Sheets(Target.Row).Name = Target.Value Else End If End Sub HTH, Paul -- "wally" wrote in message .... I have 16 sheets that are numbered 1 thru 16 on the tab. I also have a 17th sheet I have renamed "Data" (ignore quotes). I would like to type a name, (i.e., wonder) into cell a1 of the Data sheet and have that automatically change the name of sheet number 1 to "wonder". Then I would type another name (i.e., Hanger) into on the data sheet into cell a2 and that would change sheet number 2 to Hanger. I would continue to do this until all sheets are now changed to a name rather than a numer. Can this be accomplished by a formula? Thank you, Wally- Hide quoted text - - Show quoted text - Hi Paul, Followed your instructions but when I type in a name in cell A1 on sheet Data, nothing happens. I go to VBA and the first line (Private Sub Workbook, etc) is highlighted in yellow. Wally- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
automatically change worksheet tab name
If you're not getting an error, while you've got the VBA window open and you
see the highlighted line, try pressing F5. -- "wally" wrote in message ... I removed stop and the same thing happens. On Jun 26, 5:03 pm, "PCLIVE" wrote: Oops. Sorry about that. Remove "Stop" from the beginning of the code. That was there for testing purposes. -- "wally" wrote in message ... On Jun 26, 4:12 pm, "PCLIVE" wrote: This will need to be done using VBA. Press Alt+F11 to enter the VB Editor. On the left, you should see your workbook listed as "VBAProject(YourWorkbook)". If there is a plus to the left of it, click it to make it a minus which expands the list as well. Do the same thing for "Microsoft Excel Objects". Double-click on ThisWorkbook. Paste the following code in the right-hand pane. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Stop If ActiveSheet.Name = "Data" And Sheets(Target.Row).Name < "Data" _ Then If Target.Column = 1 And Target.Value < "" Then Sheets(Target.Row).Name = Target.Value Else End If End Sub HTH, Paul -- "wally" wrote in message ... I have 16 sheets that are numbered 1 thru 16 on the tab. I also have a 17th sheet I have renamed "Data" (ignore quotes). I would like to type a name, (i.e., wonder) into cell a1 of the Data sheet and have that automatically change the name of sheet number 1 to "wonder". Then I would type another name (i.e., Hanger) into on the data sheet into cell a2 and that would change sheet number 2 to Hanger. I would continue to do this until all sheets are now changed to a name rather than a numer. Can this be accomplished by a formula? Thank you, Wally- Hide quoted text - - Show quoted text - Hi Paul, Followed your instructions but when I type in a name in cell A1 on sheet Data, nothing happens. I go to VBA and the first line (Private Sub Workbook, etc) is highlighted in yellow. Wally- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I change the source worksheet for 100 charts automatically. | Charts and Charting in Excel | |||
Automatically change sequential worksheet names in formulas | Excel Worksheet Functions | |||
worksheet tabs automatically change? | Excel Worksheet Functions | |||
How do i get the excel worksheet to automatically add or change c. | Excel Discussion (Misc queries) | |||
how do i make a date change automatically if i change one before . | Excel Discussion (Misc queries) |