How can I automatically insert the file name into the worksheet tab in Excel
2003? Or for that matter, any data in a cell into the tab label? (Without
manually typing it in?)

Private Sub Worksheet_Change(ByVal Target As Range)
''autoname the worksheet Tab from value in A1 or as filename
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
If .Value < "" Then
Me.Name = ActiveWorkbook.Name
'if you want value from a cell to be the sheet name
'Me.Name = .Value
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP

On Wed, 11 Jul 2007 09:16:03 -0700, Bob wrote:

How can I automatically insert the file name into the worksheet tab in Excel
2003? Or for that matter, any data in a cell into the tab label? (Without
manually typing it in?)

Gord,

thanks for your reply. I'm not sure what to do with this. Where would I
enter this piece of code?

Bob

"Gord Dibben" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
''autoname the worksheet Tab from value in A1 or as filename
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
If .Value < "" Then
Me.Name = ActiveWorkbook.Name
'if you want value from a cell to be the sheet name
'Me.Name = .Value
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP

On Wed, 11 Jul 2007 09:16:03 -0700, Bob wrote:

How can I automatically insert the file name into the worksheet tab in Excel
2003? Or for that matter, any data in a cell into the tab label? (Without
manually typing it in?)

Apologies Bob.

This is sheet event code.

Right-click on the sheet tab and "View Code".

Copy/paste into that sheet module.

Edit the "A1" to whatever cell you want to use.

Note the single quote marks before the two lines. They will not run.

I stuck them in there so's you have a choice of a cell value or the workbook.

Remove the quote from 'Me.Name = .Value and add a quote to

Me.Name = ActiveWorkbook.Name

'if you want value from a cell to be the sheet name.....is just a comment.


Gord


On Wed, 11 Jul 2007 17:26:01 -0700, Bob wrote:

Gord,

thanks for your reply. I'm not sure what to do with this. Where would I
enter this piece of code?

Bob

"Gord Dibben" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
''autoname the worksheet Tab from value in A1 or as filename
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
If .Value < "" Then
Me.Name = ActiveWorkbook.Name
'if you want value from a cell to be the sheet name
'Me.Name = .Value
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP

On Wed, 11 Jul 2007 09:16:03 -0700, Bob wrote:

How can I automatically insert the file name into the worksheet tab in Excel
2003? Or for that matter, any data in a cell into the tab label? (Without
manually typing it in?)