dynamic worksheet name

[annajean]

I want to make the worksheet name automatically change to the value in a cell
on that sheet. For example i have a job time sheet with the name of the job
in cell b5; I want the name of the worksheet on the sheet tab at the bottom
to automatically change to the value in cell b5. I am using office 2003 and
office 2007. Any help would be very appreciated.

 

[Gord Dibben]

Private Sub Worksheet_Change(ByVal Target As Range)
'autoname the worksheet Tab from value in a cell
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("B5")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
If .Value <> "" Then
Me.Name = .Value
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub

Right-click on the sheet tab and "View Code".

Copy.paste the code into that module.

Alt + q to return to Excel.

If the name in B5 is the result of a calculation change the event type to

Private Sub Worksheet_Calculate()


Gord Dibben MS Excel MVP