Using VB to change sheet name to match cell contents
 

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

Using VB to change sheet name to match cell contents
 

Hi
try
sub foo()
with activesheet
.name=.range("A1").value
end with
end sub

--
Regards
Frank Kabel
Frankfurt, Germany


Gary Paxson wrote:
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

Using VB to change sheet name to match cell contents
 

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)

"Gary Paxson" wrote in message
...
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

Using VB to change sheet name to match cell contents
 

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


Gary Paxson wrote:
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
.

Using VB to change sheet name to match cell contents
 

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)

"Gary Paxson" wrote
in message
...
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


.