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