Thanks Don i saw your second post,
That solved that problem. Now instead I get the error message:
Run time error '7'
Out of memory

The workbook already contains quite a few macros and formulas but by now way
gigantic (6 different macros and 12 sheets with 50 rows in each with formulas)
What might be the problem
Thanks again for your promp replies!

"Don Guillett" skrev:

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