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

http://wiki.ittoolbox.com/index.php/Excel_Macro_to_Rename_Worksheet_Tabs

will do the rename part for you

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.

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

Worked perfectly... Thanks a bunch.

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"

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

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

Select allOpen in new window

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

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

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

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

Select allOpen in new window

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

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

Select allOpen in new window

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

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