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"