Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically name worksheet tab w/cell formula
I would like to be able to automatically name my worksheet
tabs. Is there a cell formula or macro that I can use so that: If: The contents of cell A1 is 'January 1, 2004' Then: The worksheet tab automatically is changed to 'January 1, 2004' Also, can anyone recommend books on VB programming (beginner level) for Excel? Thanks, Bill |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically name worksheet tab w/cell formula
Hi Bill
this requieres VBA. You could use the worksheet_change event. E.g. the following code will change the tab name based of the value in cell A1. Put this in your worksheet module: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub On Error GoTo CleanUp: With Target If .Value < "" Then Me.Name = .Value End If End With CleanUp: Application.EnableEvents = True End Sub -- Regards Frank Kabel Frankfurt, Germany Bill B wrote: I would like to be able to automatically name my worksheet tabs. Is there a cell formula or macro that I can use so that: If: The contents of cell A1 is 'January 1, 2004' Then: The worksheet tab automatically is changed to 'January 1, 2004' Also, can anyone recommend books on VB programming (beginner level) for Excel? Thanks, Bill |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically name worksheet tab w/cell formula
One way:
Put this in the ThisWorkbook code module (right-click on the workbook title bar and choose View Code): Private Sub Workbook_SheetChange( _ ByVal Sh As Object, ByVal Target As Excel.Range) With Target(1) If Not Intersect(.Cells, Sh.Range("A1")) Is Nothing Then On Error Resume Next Sh.Name = .Value On Error GoTo 0 End If End With End Sub For a real beginner level VBA book, John Walkenbach's "Excel 2000 Programming for Dummies" may be in your library. However, if you spend any time at it, you'll quickly get past that. Two others I recommend: John's "Excel 2002 Power Programming with VBA" and Bovey, Bullen, Green and Rosenberg's "Excel 2002 VBA: Programmers Reference" In article , "Bill B" wrote: I would like to be able to automatically name my worksheet tabs. Is there a cell formula or macro that I can use so that: If: The contents of cell A1 is 'January 1, 2004' Then: The worksheet tab automatically is changed to 'January 1, 2004' Also, can anyone recommend books on VB programming (beginner level) for Excel? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically name worksheet tab w/cell formula
You can use the code:
Sheets(i).Name = Range("A1").value If in A1 you have some value the workshhet will update aftwer you willl run the Macro --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatically copy a cell from one worksheet to another | Excel Discussion (Misc queries) | |||
Move cell data to another worksheet cell automatically. | New Users to Excel | |||
Changing worksheet name within a formula (automatically) | Excel Discussion (Misc queries) | |||
How I can set all cell in the worksheet rounddown automatically? | Excel Worksheet Functions | |||
how do I display the name of a worksheet automatically in a cell? | Excel Discussion (Misc queries) |