Using VB to change sheet name to match cell contents

G

Gary Paxson

Is there an easy way using VB to change the currently
active sheet's name to a cell value?

Scenario:
---------
I have a time reporting sheet which uses an employee
name validation pulldown to select and show work time
statistics for any of my staff.

I'd like to create a complimentary VB function which
changes the sheet's name to match that of the currently
selected employee, preferably via a changed cell action,
but I'd settle for creating a button control to accomplish
the sheet name change.

Any suggestions or code examples would be appreciated

Thx - GP
 
B

Bob Phillips

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1")) Is Nothing Then
With Target
me.name = target.value
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
G

Gary Paxson

This worked beautifully! Thanks Frank
-----Original Message-----
Hi
try
sub foo()
with activesheet
.name=.range("A1").value
end with
end sub

--
Regards
Frank Kabel
Frankfurt, Germany



.
Click to expand...
 
G

Gary Paxson

Thanks Bob - exactly what I was looking for!
-----Original Message-----
Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1")) Is Nothing Then
With Target
me.name = target.value
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)




.
Click to expand...
 
Top