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

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

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

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

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 -

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 -

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"

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"

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"

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