automatically change worksheet tab name

[wally]

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

 

[PCLIVE]

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]

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

--






- 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

 

[PCLIVE]

Oops. Sorry about that.
Remove "Stop" from the beginning of the code. That was there for testing
purposes.



--

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

--






- 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

 

[wally]

I removed stop and the same thing happens.

Oops.  Sorry about that.
Remove "Stop" from the beginning of the code.  That was there for testing
purposes.

--







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 -

 

[PCLIVE]

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]

If you're not getting an error, while you've got the VBA window open and you
see the highlighted line, try pressing F5.

--


I removed stop and the same thing happens.






- Show quoted text -

I pressed f5, error message: "Compile Error Block If Without End If"

 

[PCLIVE]

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

--

If you're not getting an error, while you've got the VBA window open and
you
see the highlighted line, try pressing F5.

--


I removed stop and the same thing happens.






- Show quoted text -

I pressed f5, error message: "Compile Error Block If Without End If"

 

[PCLIVE]

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

--

If you're not getting an error, while you've got the VBA window open and
you
see the highlighted line, try pressing F5.

--


I removed stop and the same thing happens.






- Show quoted text -

I pressed f5, error message: "Compile Error Block If Without End If"

 

[wally]

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 blockif
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

--







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