Worksheet Title in Cell?

B

BillT

Is there a way to set a cell to read a worksheet title? I am trying to set
up employee scheduling and would like to name each worksheet by the employee:
And my hopes are by doing so each time that I create a new worksheet and name
the worksheet the employees name is registered in a certain cell above...

Thanks
 
C

Carim

Hi Bill,

Input your title in cell A1, and Worksheet name will adjust itself ...

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const sNAMECELL As String = "A1"
Const sERROR As String = "Invalid worksheet name in cell "
Dim sSheetName As String

With Target
If Not Intersect(.Cells, Range(sNAMECELL)) Is Nothing Then
sSheetName = Range(sNAMECELL).Value
If Not sSheetName = "" Then
On Error Resume Next
Me.Name = sSheetName
On Error GoTo 0
If Not sSheetName = Me.Name Then _
MsgBox sERROR & sNAMECELL
End If
End If
End With
End Sub


HTH
Cheers
Carim
 
Top