cell E5 to be worksheet name

[bluepenink]

Hello

is it possible to have cell E5 become the worksheet or tab name?

currently cell E5 = John Doe's Performance (Revenue)

so i would like the worksheet name to equal that...can someone pls help!

thx u!

 

[Jonmo1]

Need VBA...

Try this

Sub NameSheet()
ActiveSheet.Name = Range("E5").Value
End Sub

 

[bluepenink]

lol pls excuse my anglish there

E5 = John Doe

I want the worksheet/tab name to be John Doe.

If i change John Doe, to Michael Jordon, I would like if it can change itself. thx you for your help sir!

 

[Jonmo1]

to make it happen automatically when you change the value in E5, try

Right click on the Sheet's Tab, View code
Paste the following

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$E$5" Then
    On Error Resume Next
    Me.Name = Target.Value
    If Err.Number = 1004 Then
        MsgBox "Invalid Sheet Name"
        Application.Undo
    End If
    On Error GoTo 0
End If
End Sub

now when you change the value of E5, the sheet's name will change with it.
Hope that helps.

 

[bluepenink]

Need VBA...

Try this

Sub NameSheet()
ActiveSheet.Name = Range("E5").Value
End Sub

hello

im not that good with macros, if i want this for a partiular worksheet, i.e. Sheet4....how can i re-work the macro?

and am i going to insert this on each worksheet in VBA or a module? thx u

 

[Jonmo1]

If you want it to happen for ALL sheets, then put this in the module named "ThisWorkBook"

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Address = "$E$5" Then
    On Error Resume Next
    Sh.Name = Target.Value
    If Err.Number = 1004 Then
        MsgBox "Invalid Sheet Name"
        Application.Undo
    End If
    On Error GoTo 0
End If
End Sub

 

[bluepenink]

much appreciated. thx u

is it possible to have & or - included

basially, one of my worksheets is called
John Doe's Performance Rev & Est

and it gives me an error whenever i try to use & or - or + etc

thx u

 

[Jonmo1]

It's not the symbols that are the problem, it's the length.
Limit is 31 characters.
You're only over it by 1.

For your example, I would recommend removing the apostrophe '

Sheet names with apostrophes can cause nightmares in subsequent programming.

 

[bluepenink]

Hello

i used the following code

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
  On Error GoTo Rename_Error
  If Target.Address = "$E$5" And Len(Target.Value) <> 0 Then _
    Sh.Name = Target.Value
  Exit Sub
Rename_Error:
  MsgBox "The sheet name could not be renamed. Please check " & vbCrLf & _
        "that you did not use illegal characters."
End Sub

my file is password protected thru a macro and when i opened the workbook, it does not change the worksheet name, even if the worksheet is unproctected...any suggestions?

 

[Jonmo1]

Of coarse it doesn't change the sheet name when you open file.
It only changes the sheet name when the value in E5 changes.