I used this code on the worksheet (found it on this site, I think)

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Me.[o2], Target) Is Nothing Then
On Error GoTo InvalidName
Me.Name = Target.Value
Exit Sub
End If
InvalidName:
MsgBox "Invalid sheet name."
End Sub


which worked great when I did it, renaming the sheet to the content of O2.
I've just opened it today, and although valid names work, and invalid names
call the error message, any change to any other cell also calls the error
message. What's going on?

thanks,
Andy

Because your using the worksheet change event then any change to the
worksheet is calling the macro.

You could put a button on your sheet and use this to call your name change
macro.

"foilfencingandy" wrote:

I used this code on the worksheet (found it on this site, I think)

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Me.[o2], Target) Is Nothing Then
On Error GoTo InvalidName
Me.Name = Target.Value
Exit Sub
End If
InvalidName:
MsgBox "Invalid sheet name."
End Sub


which worked great when I did it, renaming the sheet to the content of O2.
I've just opened it today, and although valid names work, and invalid names
call the error message, any change to any other cell also calls the error
message. What's going on?

thanks,
Andy

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Me.[o2], Target) Is Nothing Then
On Error GoTo InvalidName
Me.Name = Target.Value
End If
Exit Sub
InvalidName:
MsgBox "Invalid sheet name."
End Sub

should fix that. You have the exit sub in the wrong place. With it in
your IF statement, if you don't meet the IF condition, you always go through
the error handler.

--
Regards,
Tom Ogilvy



"foilfencingandy" wrote:

I used this code on the worksheet (found it on this site, I think)

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Me.[o2], Target) Is Nothing Then
On Error GoTo InvalidName
Me.Name = Target.Value
Exit Sub
End If
InvalidName:
MsgBox "Invalid sheet name."
End Sub


which worked great when I did it, renaming the sheet to the content of O2.
I've just opened it today, and although valid names work, and invalid names
call the error message, any change to any other cell also calls the error
message. What's going on?

thanks,
Andy

That's brilliant, thanks. :-)

"Tom Ogilvy" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Me.[o2], Target) Is Nothing Then
On Error GoTo InvalidName
Me.Name = Target.Value
End If
Exit Sub
InvalidName:
MsgBox "Invalid sheet name."
End Sub

should fix that. You have the exit sub in the wrong place. With it in
your IF statement, if you don't meet the IF condition, you always go through
the error handler.

--
Regards,
Tom Ogilvy



"foilfencingandy" wrote:

I used this code on the worksheet (found it on this site, I think)

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Me.[o2], Target) Is Nothing Then
On Error GoTo InvalidName
Me.Name = Target.Value
Exit Sub
End If
InvalidName:
MsgBox "Invalid sheet name."
End Sub


which worked great when I did it, renaming the sheet to the content of O2.
I've just opened it today, and although valid names work, and invalid names
call the error message, any change to any other cell also calls the error
message. What's going on?

thanks,
Andy