Naming a worksheet from a cell in another worksheet

[sportsranker]

Is it possible to name a worksheet by entering the name in a cell from another worksheet in the same workbook? I am building a macro and am missing this crucial step.

I would like it so that someone else could come along and change the name in the cell and the name of the workbook would change too.

Below is the code so far:

Please Login or Register  to view this content.

Thanks.

[Stuie]

Hi sportsranker,

Quick solution is....

If you named ythe cell that you wanted to use to set the sheet name to "Name" or something relivent and then where you want the sheet name to change just add the following piece of code

Please Login or Register  to view this content.

or somthing similer to that.

[sportsranker]

Stuie, thanks for the help.

Just to clarify:

If the cell is N46 on the "Controls" workbook, then the proper code would be:

Sheets("Controls").Select
Sheets("Template (2)").Name = Range("N46").Value

I seem to get an error when I run this.

[Stuie]

Is there error telling something about naming it with no name or something, the Sheets("Template (2)").Name = Range("N46").Value part is telling it to use the value in range("N46") on the control sheet as you have selected that sheet to be active, so try adding

Please Login or Register  to view this content.

[sportsranker]

The original error went away without using the second adjustment (strangely enough).

The first line of code you mentioned works now. But if I change the text in the cell, the name of the worksheet does not change with it. Is this even possible?

Thanks.

[Stuie]

It will only change when the macro is ran,

If you want it change when the N46 is changed you will need to tap into the Worksheet_Changed event try pasting this into the worksheet where the range with the name is used

Please Login or Register  to view this content.

EDIT: its ment to be If Target.Address = "$N$46" Then
and reference the sheet number not sheet name as the name will change

so should look like

Please Login or Register  to view this content.

[sportsranker]

I am a little confused as to where I need to paste that code into the N46 on the controls worksheet?

[Stuie]

Sorry, i didnt explain it very well now i have read it back,

The sheet where you enter the name in cell n46 needs the code entered into its sheets events, in the vba editor if you look on the Projects window it shoud say for example Sheet1 ("Sheet1")

Find the sheet you need and double click it, that will bring up a window where you will be able to paste the code into

[sportsranker]

Thanks Stuie. I can take it from here.

[Stuie]

Cool no probs, any futher questions give me a shout