Automatically naming sheets

[Guest]

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

 

[Rob Bovey]

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

 

[Don Guillett]

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]

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

 

[Guest]

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:

 

[Guest]

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:

 

[Tom Ogilvy]

Hey Don,
I think I would change it to:

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

To be sure.

 

[Don Guillett]

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

 

[Guest]

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:

 

[Don Guillett]

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

 

[Don Guillett]

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

 

[Tom Ogilvy]

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.