 |
 | 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 / May 2008Changing Sheet Name
Hi, A cell (say B6) has a formula that reads the text value from another cell (say D10). so: B6 has the formula =D10 Is it possible to have VBA code so that the sheet name will be equal to the value in that cell (in my example) cell B6 Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "B6" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target Me.Name = .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.  Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Hi, > A cell (say B6) has a formula that reads the text value from another cell [quoted text clipped - 3 lines] > Is it possible to have VBA code so that the sheet name will be equal to > the value in that cell (in my example) cell B6 Something like this maybe... Sub ChangeSheetName() With ActiveSheet .Name = .Range("B6").Value End With End Sub Change the ActiveSheet reference if you are not running this code from the sheet you want to affect. Rick > Hi, > A cell (say B6) has a formula that reads the text value from another cell [quoted text clipped - 3 lines] > Is it possible to have VBA code so that the sheet name will be equal to > the value in that cell (in my example) cell B6 This worked as a macro. How can it be done so that it runs automatically. Bob's Suggestion did't work out! > Something like this maybe... > [quoted text clipped - 16 lines] >> Is it possible to have VBA code so that the sheet name will be equal to >> the value in that cell (in my example) cell B6 > This worked as a macro. How can it be done so that it runs automatically. > Bob's Suggestion did't work out! Worked for me first time, cut a paste. Excel 2003 Bob's code requires you to manually change the value in B6 Try this calculate event. Private Sub Worksheet_Calculate() On Error GoTo stoppit Application.EnableEvents = False With Me.Range("B6") If .Value <> "" Then Me.Name = .Value End If End With stoppit: Application.EnableEvents = True End Sub Gord Dibben MS Excel MVP >This worked as a macro. How can it be done so that it runs automatically. >Bob's Suggestion did't work out! [quoted text clipped - 19 lines] >>> Is it possible to have VBA code so that the sheet name will be equal to >>> the value in that cell (in my example) cell B6 Thanks to all of you it worked well. > Bob's code requires you to manually change the value in B6 > [quoted text clipped - 39 lines] >>>> Is it possible to have VBA code so that the sheet name will be equal to >>>> the value in that cell (in my example) cell B6 |
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.