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!

 

[bluepenink]

hello

thx for your prompt response.

in cell E5 on sheet1, the cell =D5&" District Performance"
where D5 is drop down selection of Q2, Q3, Q4

when i change that, it changes in E5, but not in the formula.

is it how i have my macro, in this workbook?

Private Sub Workbook_Open()
For c = 1 To Sheets.Count
               With Worksheets(c)
        .Protect Password:="analyst", userinterfaceonly:=True
        .EnableOutlining = True
    End With
Next c
End Sub
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

 

[bluepenink]

hello

the code above works, however, i need to go into the cell i.e. E5 and press enter for the worksheet to be refreshed.

is it possible for the macro to auto refresh?

 

[Jonmo1]

OK, it's not working because the code is triggered by manual change of the value in E5. Not via formula in E5.

If it's D5 that is manually changed by the dropdown, try

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
  On Error GoTo Rename_Error
  If Target.Address = "$D$5" And Len(Target.Value) <> 0 Then _
    Sh.Name = Sh.Range("E5").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

Hope that helps.