Hi CD,
Not sure which way round you meant.
This formula will return the sheet name.
=RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename")))
Below this code will change the sheet name of Worksheets "Sheet1" and "Sheet2", to reflect whatever is typed into cell E2.
CODE
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim strPwd As String
Dim rngSheetName As Range
strPwd = "yourpassword"
Set rngSheetName = Range("E2")
Select Case Sh.Name
Case Is = "Sheet1", "Sheet2"
If Not (Intersect(Target, rngSheetName) Is Nothing) Then
With Sh
.Unprotect Password:=strPwd
.Name = Target.Value
.Protect Password:=strPwd, DrawingObjects:=True, Contents:=True, Scenarios:=True
End With
End If
Case Else
End Select
End Sub
HTH
Stu