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

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

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?

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/