Name a worksheet tab with data in a cell

D

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.
 
N

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
 
F

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
 
N

Norman Jones

Hi Frank,

I agree.

---
Regards,
Norman



Frank Kabel said:
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
Click to expand...
 
F

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
 
D

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
 
D

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.
Click to expand...
(rightclick on the excel icon to
 
Top