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
3,081 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
  • 5
  • 3
  • 3
  • +3
16 Comments
 
LVL 7

Expert Comment

by:wittyslogan
Comment Utility
0
 

Author Comment

by:bbgocats
Comment Utility
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 80

Accepted Solution

by:
byundt earned 500 total points
Comment Utility
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
 
LVL 33

Expert Comment

by:roos01
Comment Utility
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
Comment Utility
Worked perfectly... Thanks a bunch.
0
 
LVL 7

Expert Comment

by:wittyslogan
Comment Utility
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 80

Expert Comment

by:byundt
Comment Utility
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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 45

Expert Comment

by:patrickab
Comment Utility
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
Comment Utility
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
Comment Utility
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 80

Expert Comment

by:byundt
Comment Utility
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
Comment Utility
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 80

Expert Comment

by:byundt
Comment Utility
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
Comment Utility
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 80

Expert Comment

by:byundt
Comment Utility
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

763 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now