how do i rename worksheet to equal cell name

[Guest]

Hi. I'm try to figure out a function that will automate renaming the
worksheet by reading a cell entry. For instance....if i have a timesheet
format, I would like the user to be able to enter their name in a cell (a2,
for instance) and then have a macro re-name the worksheet tab to reflect
their name.

I know this is possible by browsing through the VB properties. I am just
unsure of the correct code syntax.

Thanks loads!

 

[Guest]

Right click on your worksheet tab, select view code and paste the following
into the code window:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ExitSub
If Not Application.Intersect(Me.Range("A2"), _
Target) Is Nothing Then _
Me.Name = Me.Range("A2").Value

ExitSub:

End Sub


If it is possible for cell A2 to move around (user inserts a row or column),
I would suggest using a named range (name cell A2 using the name box in upper
left corner). Then, in the above code change "A2" to "Name" - whatever your
name may be.

 

[Piranha]

Hi. I'm try to figure out a function that will automate renaming the
worksheet by reading a cell entry. For instance....if i have a
timesheet
format, I would like the user to be able to enter their name in a cell
(a2,
for instance) and then have a macro re-name the worksheet tab to
reflect
their name.

I know this is possible by browsing through the VB properties. I am
just
unsure of the correct code syntax.

Thanks loads!

Miss,
Where cell A5 is the cell in which the name is entered.
Put code into the Sheet module.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A5")) 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

Dave