renaming worksheet with cell contents

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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.
 
That's brilliant, thanks. :-)

Tom Ogilvy said:
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 said:
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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Trying to run excel vba code when cell value changes from empty to entered value. 1
Macro converts time entered in column E and F from Indian time to CST( without daylight saving) 1
Junp start my Select Case Macro 12
Save As VBA 2
Update sheetname from cell, automatically 13
changing tabs from a cell from a difference sheet 2
Copying Cell Conflict with Worksheet Change to UpperCase 4
Rename Sheets but..... 2

Back
Top