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! |
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! |
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 |
All times are GMT +1. The time now is 08:32 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com