 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / July 2008Rename tab on cell event
Looking for code that would simply, on the fly, depending what was entered in a cell at anytime, any amount of times, the tab name would be renamed to what the cell contents are. I have seen some pretty elaborate ways of renaming tabs. I am new at Excel and any help would be appreciated. Thanks! Hi, Right click your sheet tab, view code and paste this in then every tiome A1 is changed to a 'legal' worksheet name the sheet will be renamed. Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo enditall ActiveSheet.Name = Range("A1").Value enditall: End Sub Mike > Looking for code that would simply, on the fly, depending what was entered in > a cell at anytime, any amount of times, the tab name would be renamed to what > the cell contents are. > > I have seen some pretty elaborate ways of renaming tabs. I am new at Excel > and any help would be appreciated. Thanks! Mike, That works really slick. Question... If someone changes the cell to a nothing or null value, it does not rename the sheet. Can there be a way to go back to say "sheet 1" or "sheet 2" if cell becomes empty? John G. > Hi, > [quoted text clipped - 15 lines] > > I have seen some pretty elaborate ways of renaming tabs. I am new at Excel > > and any help would be appreciated. Thanks! Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo enditall Application.EnableEvents = False With Me If .Range("A1") = "" Then .Name = .Name Else .Name = .Range("A1").Value End If End With enditall: Application.EnableEvents = True End Sub Gord Dibben MS Excel MVP >Mike, > [quoted text clipped - 23 lines] >> > I have seen some pretty elaborate ways of renaming tabs. I am new at Excel >> > and any help would be appreciated. Thanks! Thanks Gord and Mike. Making life simpler, or complex, depends on what else this leads to. Thanks a bunch! > Private Sub Worksheet_Change(ByVal Target As Range) > On Error GoTo enditall [quoted text clipped - 39 lines] > >> > I have seen some pretty elaborate ways of renaming tabs. I am new at Excel > >> > and any help would be appreciated. Thanks! Gord, This returns the right value only the cell shows a square symbol between the round number and the fraction. I tried to paste it here, but it did not come over the same. > Private Sub Worksheet_Change(ByVal Target As Range) > On Error GoTo enditall [quoted text clipped - 39 lines] > >> > I have seen some pretty elaborate ways of renaming tabs. I am new at Excel > >> > and any help would be appreciated. Thanks! John I think you have responded to the wrong posting. No round numbers or fractions that I can see in my post. But square symbols are usually linefeeds within a cell. Gord >Gord, >This returns the right value only the cell shows a square symbol between the [quoted text clipped - 44 lines] >> >> > I have seen some pretty elaborate ways of renaming tabs. I am new at Excel >> >> > and any help would be appreciated. Thanks! Great sub, however in MY case I need 4 sheets to be renamed based on cells in sheet 1. Can anyone help? Sorry, I should have been more specific. I have a that has 8 sheets. Sheet 1 is the master sheet. All sheets have default names. (contractor1, contractor2....) Once I enter the contractor name into the Master sheet (C4) I want sheet 2 to update to the value in sheet 1, C2. Similar for sheet 1, D2. And for other cells in the master sheet. thanks in advance. dan G’Day Gord This works a treat and is simple to understand. I have one remaining question. The cell I am referencing (B1 in sheet 2) has a CONCATENATE function (joining "sheet1!A1,A1" ) . When I change A1, I sheet2, B2 also changes as does the Sheet2 sheet name. However, when I change the value in Sheet1!A1 there is no change to the Sheet2 sheet name, even though a B1 has changed? I have tried "F9", even closing and opening the workbook but I can't seem to force a change. Is there a simple solution? Thanks Scott > Private Sub Worksheet_Change(ByVal Target As Range) > On Error GoTo enditall [quoted text clipped - 39 lines] > >> > I have seen some pretty elaborate ways of renaming tabs. I am new at Excel > >> > and any help would be appreciated. Thanks! You need a calculate event for that. Private Sub Worksheet_Calculate() On Error GoTo enditall Application.EnableEvents = False With Me If .Range("B1") = "" Then .Name = .Name Else .Name = .Range("B1").Value End If End With enditall: Application.EnableEvents = True End Sub Assumes code is in Sheet2 and B1 has formula =CONCATENATE(Sheet1!A1,A1) Gord >GDay Gord > [quoted text clipped - 57 lines] >> >> > I have seen some pretty elaborate ways of renaming tabs. I am new at Excel >> >> > and any help would be appreciated. Thanks! Gord, You are a gentlemen and a scholar, thanks very much Scott > You need a calculate event for that. > [quoted text clipped - 79 lines] > >> >> > I have seen some pretty elaborate ways of renaming tabs. I am new at Excel > >> >> > and any help would be appreciated. Thanks! Good to hear you got sorted out. Thanks for the feedback. Gord >Gord, > [quoted text clipped - 85 lines] >> >> >> > I have seen some pretty elaborate ways of renaming tabs. I am new at Excel >> >> >> > and any help would be appreciated. Thanks! In the sheet code module, paste this in - change [A2] to your desired cell. Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, [A2]) Is Nothing _ Or [A2] = "" Then Exit Sub Dim wks As Worksheet For Each wks In Sheets If UCase([A2]) = UCase(wks.Name) Then MsgBox "Can't rename a sheet with " & [A2].Value _ & vbNewLine & "as that name already exist." Exit Sub End If Next wks On Error Resume Next ActiveSheet.Name = [A2].Value2 If Err.Number <> 0 Then MsgBox Err.Description End Sub Well it was going to be a 1 liner, but that's been taken :) Regards Robert McCurdy > Looking for code that would simply, on the fly, depending what was entered in > a cell at anytime, any amount of times, the tab name would be renamed to what > the cell contents are. > > I have seen some pretty elaborate ways of renaming tabs. I am new at Excel > and any help would be appreciated. Thanks! |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.