| 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 - |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
automatically change worksheet tab name
On Jun 26, 6:07*pm, "PCLIVE" wrote:
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 -- Hide quoted text - - Show quoted text - I pressed f5, error message: "Compile Error Block If Without End If" |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
automatically change worksheet tab name
This is the result of Wrap-around that occurred during posting. The If
Target.Column... line and the line after was originally on one line. Since wrap-around occurred, it was separated onto two lines creating a block if statement. To avoid this, I've re-done the code so that it is in block if format which should correct the wrap-around issue. Try this: 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 Else End If End Sub Does that help? Paul -- "wally" wrote in message ... On Jun 26, 6:07 pm, "PCLIVE" wrote: 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 -- Hide quoted text - - Show quoted text - I pressed f5, error message: "Compile Error Block If Without End If" |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
automatically change worksheet tab name
This is the result of Wrap-around that occurred during posting. The If
Target.Column... line and the line after was originally on one line. Since wrap-around occurred, it was separated onto two lines creating a block if statement. To avoid this, I've re-done the code so that it is in block if format which should correct the wrap-around issue. Try this: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) 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 Else End If End Sub Does that help? Paul -- "wally" wrote in message ... On Jun 26, 6:07 pm, "PCLIVE" wrote: 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 -- Hide quoted text - - Show quoted text - I pressed f5, error message: "Compile Error Block If Without End If" |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
automatically change worksheet tab name
On Jun 27, 9:34*am, "PCLIVE" wrote:
This is the result of Wrap-around that occurred during posting. *The If Target.Column... line and the line after was originally on one line. *Since wrap-around occurred, it was separated onto two lines creating a block if statement. *To avoid this, I've re-done the code so that it is in block if format which should correct the wrap-around issue. Try this: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) 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 * * Else End If End Sub Does that help? Paul -- "wally" wrote in message ... On Jun 26, 6:07 pm, "PCLIVE" wrote: 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 -- Hide quoted text - - Show quoted text - I pressed f5, error message: "Compile Error Block If Without End If"- Hide quoted text - - Show quoted text - It works great!! Thanks a million Paul, just what I wanted. Wally |
| 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) | |||
Linear Mode
