Name a worksheet tab with data in a cell

[donovan]

I have Excel 2002 and would like to rename a worksheet tab
with a cell reference. Example, name the tab "John Doe"
his name is located in cell b2. each time I change the
value in b2, the name of the tab will change accordingly.

 

[Norman Jones]

Hi Donovan,

In the worksheet's module (right-click the sheet's tab | View Code), paste
the following:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("B2")) Is Nothing Then
Me.Name = Target.Value
End If
End Sub

 

[Frank Kabel]

Hi Norman
I would use the worksheet_change or worksheet_calculate event:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit _
Sub
On Error GoTo errhandler
Application.EnableEvents = False
Me.Name = Target.Value
errhandler:
Application.EnableEvents = True
End Sub

 

[Norman Jones]

Hi Frank,

I agree.

---
Regards,
Norman

Hi Norman
I would use the ="Per "&TEXT(TODAY(),"mmm dd yyyy")or worksheet_calculate
event:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit _
Sub
On Error GoTo errhandler
Application.EnableEvents = False
Me.Name = Target.Value
errhandler:
Application.EnableEvents = True
End Sub

 

[Guest]

Thanks Norm,

I am getting an error with Me.name = Target.value

 

[Frank Kabel]

Hi
where did you put this code? it has to go in your worksheet module.
Also please tell us what exact error you got

 

[Dave Peterson]

Just to add to Frank's reply:

Frank's code goes behind the worksheet that should have this
behavior--rightclick on the worksheet tab and select veiw code. Then paste in
that code.

If you wanted this same routine to work for every worksheet in the workbook, you
could either paste the code in all of those worksheet's modules or use a
workbook event.

This one goes behind the ThisWorkbook module. (rightclick on the excel icon to
the left of File in the worksheet menu bar (file|edit|view...)

Then select view code and paste this slightly modified code:

Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Intersect(Target, Sh.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo errhandler
Application.EnableEvents = False
Sh.Name = Target.Value
errhandler:
Application.EnableEvents = True

End Sub

 

[Donovan]

Thank you all for the valuable input.

-----Original Message-----
Just to add to Frank's reply:

Frank's code goes behind the worksheet that should have this
behavior--rightclick on the worksheet tab and select veiw code. Then paste in
that code.

If you wanted this same routine to work for every worksheet in the workbook, you
could either paste the code in all of those worksheet's modules or use a
workbook event.

This one goes behind the ThisWorkbook module.

(rightclick on the excel icon to