is there anyway to have either

1) a cell drive the naming of a worksheet

or

2) a worksheet drive the naming of a cell?

thanks.

1) yes, with sheet level code like this

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then ActiveSheet.Name = Range("a1").Value
End Sub

2) yes, with
=MID(CELL("Filename"),FIND("]",CELL("Filename"))+1,255)
this only works on a workbook that has been saved.

--
Allllen


" wrote:

is there anyway to have either

1) a cell drive the naming of a worksheet

or

2) a worksheet drive the naming of a cell?

thanks.

second suggestion worked like a charm. thanks!

-brian

Allllen wrote:
1) yes, with sheet level code like this

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then ActiveSheet.Name = Range("a1").Value
End Sub

2) yes, with
=MID(CELL("Filename"),FIND("]",CELL("Filename"))+1,255)
this only works on a workbook that has been saved.

--
Allllen


" wrote:

is there anyway to have either

1) a cell drive the naming of a worksheet

or

2) a worksheet drive the naming of a cell?

thanks.

"Allllen" wrote in message

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then ActiveSheet.Name = Range("a1").Value
End Sub

I'd be careful with that one. Since the Change event occurs when a cell is
changed by VBA, it is possible that cell A1 on Sheet1 would be changed when
Sheet2 is active. Thus, your code would rename Sheet2, not Sheet1, with the
value in A1 on Sheet1.

Instead, just to be safe, use

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then Me.Name = Range("a1").Value
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)


"Allllen" wrote in message
...
1) yes, with sheet level code like this

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then ActiveSheet.Name = Range("a1").Value
End Sub

2) yes, with
=MID(CELL("Filename"),FIND("]",CELL("Filename"))+1,255)
this only works on a workbook that has been saved.

--
Allllen


" wrote:

is there anyway to have either

1) a cell drive the naming of a worksheet

or

2) a worksheet drive the naming of a cell?

thanks.