automatically change worksheet tab name

  • Thread starter Thread starter wally
  • Start date Start date
W

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
 
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
 
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 -
Click to expand...

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
 
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 -
Click to expand...

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
 
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 -
Click to expand...
 
If you're not getting an error, while you've got the VBA window open and you
see the highlighted line, try pressing F5.
 
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 -
Click to expand...

I pressed f5, error message: "Compile Error Block If Without End If"
 
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 -
Click to expand...

I pressed f5, error message: "Compile Error Block If Without End If"
 
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 -
Click to expand...

I pressed f5, error message: "Compile Error Block If Without End If"
 
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 -
Click to expand...

It works great!! Thanks a million Paul, just what I wanted.
Wally
 
Back
Top