Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
MissSunshineKiss
 
Posts: n/a
Default how do i rename worksheet to equal cell name

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!
  #2   Report Post  
JMB
 
Posts: n/a
Default

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.




"MissSunshineKiss" wrote:

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!

  #3   Report Post  
Piranha
 
Posts: n/a
Default


MissSunshineKiss Wrote:
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


--
Piranha
------------------------------------------------------------------------
Piranha's Profile: http://www.excelforum.com/member.php...o&userid=20435
View this thread: http://www.excelforum.com/showthread...hreadid=382665

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
I need to return the name of a worksheet in another worksheet cell? Rick Excel Worksheet Functions 2 June 23rd 05 01:47 AM
How can I link a cell in one worksheet to a cell in another works. EWI_Guy Excel Worksheet Functions 3 April 5th 05 09:15 PM
Refrencing another cell in a worksheet that "could" exist KimberlyC Excel Worksheet Functions 1 February 7th 05 08:09 PM
Using a cell reference to refernce worksheet in another work book [email protected] Excel Worksheet Functions 5 January 6th 05 07:26 PM
Addition to Turn cell red if today is greater or equal to date in cell Rich New Users to Excel 2 December 9th 04 03:06 AM


All times are GMT +1. The time now is 04:15 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"