?
Solved

Need to auto rename the "Sheet1" name on tab from an assigned field in worksheet.  What is the code to do this?

Posted on 2007-12-05
16
Medium Priority
?
3,111 Views
Last Modified: 2012-08-14
I have a worksheet that I would like to rename the "Sheet1" tab with an assigned name from a field (ie. B1).  This name will then be pulled to a summary sheet to update a summary page with data from the assigned worksheet.  How can I accomplish this task in excel.  Thanks.
0
Comment
Question by:bbgocats
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
  • +3
16 Comments
 
LVL 7

Expert Comment

by:wittyslogan
ID: 20413525
0
 

Author Comment

by:bbgocats
ID: 20413581
Where do I put this code?  I'm new at excel, but can find my way around ok.  I need this to only apply to one sheet and not all sheets.  I need each sheet to name itself accordingly.  If you would like to email me directly, I can be reached at <<e-mail address removed per EE policy, byundt--Microsoft Excel Zone Advisor>>.  Thanks for the assistance.
0
 
LVL 81

Accepted Solution

by:
byundt earned 2000 total points
ID: 20413808
You need to put the macro in the code pane for the worksheet. It will run when the user changes the value in cell B1. It will not run if cell B1 changes value due to a formula.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim cel As Range
Set cel = [B1]            'This cell will be used to rename the worksheet
If Intersect(Target, cel) Is Nothing Then Exit Sub

On Error Resume Next
If cel <> "" Then Me.Name = cel.Value
On Error GoTo 0
End Sub


To install a sub in the code pane for a worksheet:
1) Right-click the sheet tab for the worksheet
2) Choose View Code from the resulting pop-up
3) Paste the suggested code in the resulting module sheet
4) ALT + F11 to return to the worksheet

If the above procedure doesn't work, then you need to change your macro security setting. To do so, open the Tools...Macro...Security menu item. Choose Medium, then click OK.

Brad


P.S.
I removed the e-mail address from your question. You can put it in your user profile, but not in question threads. This is to protect you from receiving spam.
byundt--Microsoft Excel Zone Advisor
0
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
LVL 33

Expert Comment

by:Jeroen Rosink
ID: 20413851
The easiest code to rename a worksheet is:
Activesheet.Name = [B1]

This can be done by a macro in a new module:
1. open VBA editor (ALT+F11)
2. insert new module
3. copy/paste code like:
Sub RenameSheet()
    Activesheet.Name = [B1]
End Sub

To rename a sheetname when a cell like B1 is changed then you might think of  a trigger of the worksheet change event. This can be done with some code like:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim X As Object
Dim rg As Range, cel As Range
Dim strFormat As String
Set rg = Intersect(Target, Range("B1"))
If rg Is Nothing Then Exit Sub

On Error Resume Next
Set X = ActiveWorkbook.Sheets(sname)
If Err > 0 Then
    ActiveSheet.Name = [B1]
Else
    MsgBox "Sheet " & [B1] & " Already exist!"
End If

End Sub

To use this code you have to:
1. open VBA editor (ALT+F11)
2. double click in project explorer of the editor on the sheet where it should take effect
3. copy/paste the  code mentioned above.

I hope this works for you
regards,
Jeroen
0
 

Author Closing Comment

by:bbgocats
ID: 31412908
Worked perfectly... Thanks a bunch.
0
 
LVL 7

Expert Comment

by:wittyslogan
ID: 20413862
go to tools:
macro
visual basic editor
copy and paste the following code there

Sub RenameTabs()
' Renames all worksheet tabs with each worksheet's cell A1 contents.
'If cell A1 has no content, then that tab is not renamed.
   
 
    If Worksheets(1).Range("B1").Value <> "" Then
        Sheets(1).Name = Worksheets(1).Range("B1").Value
    End If

   
End Sub

After this if you want to rename the sheet go to the macro area again and  this time run macro.
That should work

Just tested it and I now have sheet called "sdfsgh"

0
 
LVL 81

Expert Comment

by:byundt
ID: 20413877
Bo,
If you want each sheet in the workbook to name itself according to the value in cell B1, then I suggest using a slightly different macro in the ThisWorkbook code pane:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim cel As Range
Set cel = Sh.[B1]
If Intersect(Target, cel) Is Nothing Then Exit Sub

Select Case Sh.Index
'The next statement is optional. It allows you to exclude certain sheets from the name change
Case 4, 6    'Don't change the name of the 4th or 6th worksheet in the workbook
Case Else
    On Error Resume Next
    If cel <> "" Then Sh.Name = cel.Value
    On Error GoTo 0
End Select
End Sub


To install a sub in the code pane for ThisWorkbook:
1) ALT + F11 to open the VBA Editor
2) If you don't see a list of VBA projects on the left, then CTRL + R to open the Project Explorer
3) In the Project Explorer window, double-click ThisWorkbook to open its code pane
4) Paste the suggested code in the resulting module sheet
5) ALT + F11 to return to the spreadsheet

If the above procedure doesn't work, then you need to change your macro security setting. To do so, open the Tools...Macro...Security menu item. Choose Medium, then click OK.

Brad
0
 
LVL 45

Expert Comment

by:patrickab
ID: 20413943
bbgocats,

Here's a file with a macro (as given below) that renames each sheet with whatever is in cell B1 on that sheet. It also lists on the Summary sheet all the sheet names.

http://download.yousendit.com/5B0A74980C19A1D2

Hope that helps

Patrick
Option Explicit
' Renames all worksheet tabs with each worksheet's cell B1 contents.
'If cell B1 has no content and the Sheet is named Summery then that tab is not renamed.
 
Sub RenameTabs()
Dim i As Long
 
Sheets("Summary").Columns(1).ClearContents
Sheets("Summary").[A1] = "Sheet names"
Sheets("Summary").[A1].Font.Bold = True
 
For i = 1 To Sheets.Count
    If Worksheets(i).[B1] <> "" And Sheets(i).Name <> "Summary" Then
        Sheets(i).Name = Worksheets(i).[B1]
    End If
    Sheets("Summary").[A65536].End(xlUp).Offset(1, 0) = Worksheets(i).[B1]
Next i
 
Sheets("Summary").Activate
Sheets("Summary").Columns(1).AutoFit
    
End Sub

Open in new window

0
 
LVL 45

Expert Comment

by:patrickab
ID: 20413949
and the code was:-

Sub RenameTabs()
Dim i As Long

Sheets("Summary").Columns(1).ClearContents
Sheets("Summary").[A1] = "Sheet names"
Sheets("Summary").[A1].Font.Bold = True

For i = 1 To Sheets.Count
    If Worksheets(i).[B1] <> "" And Sheets(i).Name <> "Summary" Then
        Sheets(i).Name = Worksheets(i).[B1]
    End If
    Sheets("Summary").[A65536].End(xlUp).Offset(1, 0) = Worksheets(i).[B1]
Next i

Sheets("Summary").Activate
Sheets("Summary").Columns(1).AutoFit
   
End Sub
0
 
LVL 45

Expert Comment

by:patrickab
ID: 20413967
bbgocats - If you want to Summarise the worksheet names you will need to adopt a macro which includes that part of it - as in mine...  Patrick
0
 

Expert Comment

by:geek-goddess
ID: 22016773
Hello All,
I have tried all the code snippets in this question; however, I still not have achieved the solution that I need.  Patrick's link http://download.yousendit.com/5B0A74980C19A1D2 is no longer good.  

Basically, I need a statement to rename a sheet/tab when a cell is changed with a formula, additionally I am guessing that I will also need to trigger a worksheet change event.

I thought roos01's solution was correct, but after trying that one and all the others I still have not succeeded.

I enter a date in cell A1 in Sheet 1,  then 7 is added to it and it's populated into cell A1 in Sheet2, additionally 14 is then added to it and that new date is populated into A1 on Sheet 3.  Finally, the date in cell A1 on Sheet 1 is renamed on the tab on Sheet 1 (I had accomplished this before ever getting here).

However, none of the other tab names have changed.

So to clarify,
Sheet 1 A1 is 1/1/08 and the tab is renamed 1/1/08,
Sheet 2 A1 is 1/8/08 but the tab is still Sheet 2,
Sheet 3 A1 is 1/15/08 but the tab is still Sheet 3...

One more interesting point, if I choose to populate new data into cell A1 on Sheet 1, it does not change the tab name again, but it does change the dates on all the subsequent worksheets/tabs.

Please advise, if I need to post a new question with points.

Thanks in Advance,
Beth



0
 
LVL 81

Expert Comment

by:byundt
ID: 22016998
Beth,
If the inputs to your formula are all on the same worksheet that you want to rename, then you can use the .Precedents property to trap a change to the result of a formula. If the inputs are on a different worksheet, this approach won't work, however.

The suggested macro must go in the code pane for the worksheet. It will not work at all if installed anywhere else. You'll need to change the one statement setting rgTarget; instead of B1, point to the cell that contains your formula for the worksheet name.

Brad
Private Sub Worksheet_Change(ByVal Target As Range)
Dim X As Worksheet
Dim rgTarget As Range, cel As Range
Dim strFormat As String
Dim sName As String
Set rgTarget = [B1]     'Watch the formula in this cell for changes in result. Rename worksheet accordingly.
sName = rgTarget.Value
 
On Error Resume Next
Set rgTarget = Intersect(Target, rgTarget.Precedents)
If rgTarget Is Nothing Then
    Err.Clear
    Exit Sub
End If
Set X = ActiveWorkbook.Sheets(sName)
On Error GoTo 0
 
If X Is Nothing Then
    Err.Clear
    ActiveSheet.Name = [B1]
Else
    MsgBox "Worksheet " & [B1] & " Already exists!"
End If
 
End Sub

Open in new window

0
 

Expert Comment

by:geek-goddess
ID: 22017300
I am sorry, but this doesn't change the tab on the first worksheet nor any of the others.

Additionally, I am not sure what you mean by "If the inputs to your formula are all on the same worksheet that you want to rename, then you can use the .Precedents property to trap a change to the result of a formula."  The inputs to formula and Precedents property have me thrown.

My formulas:
Sheet 1 A1 = 1/1/08
Sheet 2 A1 = SUM(Sheet1!A1,7)
Sheet 3 A1 = SUM(Sheet1!A1,14)

When I change the date manually on Sheet 1 A1 then A1 on all the subsequent pages change accordingly.  So did the tab on Sheet 1 (prior to the above code), but none of the other tabs change.

Below is my tab rename formula for the first sheet.  
Can I modify this to work for the entire workbook/all worksheets?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$1" Then
If IsEmpty(Target) Then Exit Sub
ActiveSheet.Name = Format(Target, "mm-dd-yyyy")
End If
End Sub
0
 
LVL 81

Expert Comment

by:byundt
ID: 22017813
Beth,
With a user input to cell A1 on the first worksheet, the Worksheet_Change event sub below will rename the first three sheets in the workbook.

This code should go only in the code pane for the first worksheet. You do not need it in the code pane for the other two worksheets.

Brad
Private Sub Worksheet_Change(ByVal Target As Range)
Dim targ As Range
Dim i As Long
Set targ = [A1]     'Changes to this cell will rename all three worksheets
 
If Not Intersect(targ, Target) Is Nothing Then
    If IsDate(targ) Then    'Rename the first three worksheets by the dates in their respective cell A1
        For i = 1 To 3
            Worksheets(i).Name = Format(Worksheets(i).Range(targ.Address).Value, "mm-dd-yyyy")
        Next
    End If
End If
End Sub

Open in new window

0
 

Expert Comment

by:geek-goddess
ID: 22018415
THANK YOU so much that worked great!!!
Now, do you know of a place (possibly a website) where I can go to get more info. (details and examples) of Excel VB(A) code?  I'd like to acquire some knowledge on the Objects and Procedures built into VB in Excel.  If you know of any good books that would be cool, too.

I've written a pretty cool Macro to automate some tasks that scored me BIG points w/the execs at work.
Now I looking to do even bigger stuff.

Much Appreciation and I wish that I could assign you some points or a grade.

Ciao,
Beth
0
 
LVL 81

Expert Comment

by:byundt
ID: 22018622
Beth,
You really can learn an awful lot by reading the posts in the Excel Zone at Experts-Exchange. We've had a number of people start off posting relatively amateurish code, but who became pretty competent after a few months of watching how others did it and trying to solve problems on their own. You'll see excellent code examples posted by almost all the guys in the Excel Zone Hall of Fame.

Most of us refer to code examples from one the following three web sites:
John Walkenbach http://j-walk.com/ss/excel/tips/index.htm
Chip Pearson http://www.cpearson.com/excel/topic.htm
David McRitchie http://www.mvps.org/dmcritchie/excel/excel.htm

VBA Express offers self-paced training http://www.vbaexpress.com/training.htm 

If you're partial to books, John Walkenbach has a series called Excel xxxx Power Programming with VBA; this book is updated for each new version of Excel. Most of us have copies of that. http://j-walk.com/ss/books/xlbook25.htm

Brad
0

Featured Post

ATEN's HDBaseT Presentation at InfoComm 2017

Hear ATEN Product Manager YT Liang review HDBaseT technology, highlighting ATEN’s latest solutions as they relate to real-world applications during her presentation at the HDBaseT booth at InfoComm 2017.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question