Naming tabs using Defined Name

[pvr928]

Is there any way to name a worksheet tab using a Defined Name?

Ie if the Defined Name is "Project A", then the macro/function will name the tab "Project A"?

Any help greatly appreciated.

Cheers

pvr928

 

[ken2step]

Try placing this code in your ThisWorkbook module. It should rename your sheets as the value in A1 is changed.

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

 

[pvr928]

Thanks ken2step - works like a charm!

Cheers

pvr928