Did you see my 2nd post to change back to me.name

--
Don Guillett
SalesAid Software

"Adam" wrote in message
...
When I change the cell value in the sheet that c4 is referring to it
changes
the name of the sheet I am changing in and not the sheet where i put the
code. I assume that it has something to do with ActiveSheet.Name?

"Don Guillett" skrev:

A worksheet_change event only fires when the referenced cell(s) change
by
input. So, try this.

Private Sub Worksheet_Calculate()
ActiveSheet.Name = Range("c5")
End Sub

--
Don Guillett
SalesAid Software

"Adam" wrote in message
...
In cell c4 i have a link to a cell in another sheet
In cell c5 i have the formula ="OF_"&C4.
How do I get excel to automatically rename the sheet to the content i
C5.
I have tried right click the sheet and select 'view code' and pasted:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
On Error Resume Next
With Target
If .Address = "$A$1" Then
Me.Name = .Value
End If
End With
Application.EnableEvents = True
End Sub

into it.
It seems that this macro only works when you enter the text manually
into
the cell c5 i.e. it is not automatically updated when I change the
content
of
the cell which c4 refers to.
Any ideas?

Also, will there be a problem when I protect the sheet and the cell C5
has
the format 'locked', and if so can I get around that without having to
unlock
the cell.
Thanks
/Adam