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

Hi Adam,

Try putting your code in the Worksheet_Calculate event instead. This
will fire any time cell C5 is recalculated. As far as protection goes, the
only time you would have a problem is if you protected the Workbook with the
Windows argument set to True. In that case you would have to unprotect the
workbook at the beginning of your event procedure a protect it again at the
end.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

"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

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

change to me.name to avoid changing the name of the linked sheet when it is
changed.

--
Don Guillett
SalesAid Software

"Don Guillett" wrote in message
...
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

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

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

What am I doing wrong?

"Don Guillett" skrev:

change to me.name to avoid changing the name of the linked sheet when it is
changed.

--
Don Guillett
SalesAid Software

"Don Guillett" wrote in message
...
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

Hey Don,
I think I would change it to:

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

To be sure.

--
Regards,
Tom Ogilvy


"Don Guillett" wrote in message
...
change to me.name to avoid changing the name of the linked sheet when it
is
changed.

--
Don Guillett
SalesAid Software

"Don Guillett" wrote in message
...
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

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

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

Thanks Tom but I already did that at 7:41 post

--
Don Guillett
SalesAid Software

"Tom Ogilvy" wrote in message
...
Hey Don,
I think I would change it to:

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

To be sure.

--
Regards,
Tom Ogilvy


"Don Guillett" wrote in message
...
change to me.name to avoid changing the name of the linked sheet when it
is
changed.

--
Don Guillett
SalesAid Software

"Don Guillett" wrote in message
...
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

I fail to see how that particular macro would cause the problem. Check here.
http://www.decisionmodels.com/memlimitsc.htm
--
Don Guillett
SalesAid Software

"Adam" wrote in message
...
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

change to me.name to avoid changing the name of the linked sheet when it
is changed.

That's not what I read, or I wouldn't have suggested using ME on both sides
of the equation to prevent giving the sheet the wrong name.

--
Regards,
Tom Ogilvy

"Don Guillett" wrote in message
...
Thanks Tom but I already did that at 7:41 post

--
Don Guillett
SalesAid Software

"Tom Ogilvy" wrote in message
...
Hey Don,
I think I would change it to:

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

To be sure.

--
Regards,
Tom Ogilvy


"Don Guillett" wrote in message
...
change to me.name to avoid changing the name of the linked sheet when
it
is
changed.

--
Don Guillett
SalesAid Software

"Don Guillett" wrote in message
...
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