tab naming

[McNamara.BrianT]

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]

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.

 

[McNamara.BrianT]

second suggestion worked like a charm. thanks!

-brian

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

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.

 

[Chip Pearson]

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)

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

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.