Rename a sheet tab when cell contents change

[Michelle]

Hi all,

I would like to rename a sheet tab when a specific cell's contents
change...is this possible to code? I am using Excel 2003 (but need some
backward compatibilty...)

TIA,

Miki

 

[Don Guillett]

activesheet.name=range("b1")

 

[Michelle]

activesheet.name=range("b1")

Thanks, Don...

What if I want to name a sheet that isn'T the activesheet?
worksheets(XX).name doesn't seem to work, but then I don't have a good
trigger yet.

What event can I use to trigger this change?

Miki

 

[Don Guillett]

worksheets("XX").name ="New Name"

 

[Dave Peterson]

Just one sheet?

If yes, then rightclick on the worksheet tab and choose View code. Paste this
in:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

On Error Resume Next
Me.Name = Me.Range("a1").Value
If Err.Number <> 0 Then
MsgBox "cannot rename sheet to this name"
Err.Clear
End If
On Error GoTo 0

End Sub

It should work with xl97 up.

 

[Michelle]

Just one sheet?

If yes, then rightclick on the worksheet tab and choose View code. Paste this
in:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

On Error Resume Next
Me.Name = Me.Range("a1").Value
If Err.Number <> 0 Then
MsgBox "cannot rename sheet to this name"
Err.Clear
End If
On Error GoTo 0

End Sub

It should work with xl97 up.

Thank you very much...that was exactly what I was looking for!

Miki