Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I need to return the name of a worksheet in another worksheet cell? | Excel Worksheet Functions | |||
How can I link a cell in one worksheet to a cell in another works. | Excel Worksheet Functions | |||
Refrencing another cell in a worksheet that "could" exist | Excel Worksheet Functions | |||
Using a cell reference to refernce worksheet in another work book | Excel Worksheet Functions | |||
Addition to Turn cell red if today is greater or equal to date in cell | New Users to Excel |