VBA rename sheet.

[paulrm906]

Hello everyone

I have managed to get a VBA to change the name of a sheet for me whic
is now displayed below. The below is thanks to HTH


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Worksheets(" Name = Range("K1").Value

End Sub

But the problem now is after it as changed the name from "Temp 11" t
Range name in cell K1 the above formula now keeps looking for Shee
name "Temp 11" everytime I open the sheet. Could someone please hel
tell how to tell it to ignor if unable to find the sheet name "Tem
11". I am only new at this but I am a very persistant person and pla
on learning as much as I can.

Paul Maynard
Moscow
Russia

 

[paul]

probably should save as that name...

 

[Bob Phillips]

That code errors for me. What formula are you referring to?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Dave Peterson]

Are you trying to rename the same sheet that owns this code?

If yes:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Me.Name = Me.Range("K11").Value
If Err.Number <> 0 Then
MsgBox "Rename failed"
Err.Clear
End If
On Error GoTo 0
End Sub

If wanted to rename a different sheet in the workbook, you could use the code
name. That's the name you see in the project explorer.

CodeName WorksheetName
Sheet3 (WhatYouSeeOnTheTab)

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Sheet3.Name = Me.Range("K11").Value
If Err.Number <> 0 Then
MsgBox "Rename failed"
Err.Clear
End If
On Error GoTo 0
End Sub

I used the worksheet_change event. You were trying to rename the sheet each
time you changed selection. I didn't understand that.