Rename a worksheet based on a Cell?????

[mbotts2332]

Is it possible to rename a worksheet, but have it renamed automatically, and have it be renamed to what is entered into a cell in another worksheet automatically?

 

[Juan Pablo González]

Yes, it can be done, with macros, is that a possible solution ?

 

[mbotts2332]

Yes Macro's are possible, and that was what I was figuring that needed to be used. I will try creating a macro, any tips would be helpful.
Thanks!

 

[Tom Urtis]

Not to step on Juan, but VBA would be the only way to do this. If you mean that, for instance, you want the name of the sheet tab for Sheet2 to change when a cell, say A1 in Sheet1 is changed, then enter this code in the module of Sheet1. Note that we need to use the VBA sheet object code in referencing the sheet being changed.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
Sheet2.Name = [A1].Value
End Sub

 

[Juan Pablo González]

Ok then. Here are my assumptions, you have two sheets, one, where you will change the cells, in order to change the sheet names, named "Master", and the child sheets.

In this example I'll link the cell Master!A1 with the sheet 1 on the workbook.

Right click on the Master's tab, and select "View code". In there, paste this code:

<pre>Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then Sheets(1).Name = Target.Value
End Sub</pre>The 1 in Sheets means the first sheet, according to its position.

 

[sagain2k]

Here's another example, which will save the old sheet name in the windows registry so it will still work when you come back to it after closing/reopening Excel. Note that you have to run the first part setting the registry variable and initial sheet name only once, then must disable that part from then on. This macro could then be called as a result of opening a workbook or some other event. Hope this helps... Cheers,
--Ray

Sub namesheettest()
'THIS RANAMES A WORKSHEET TO THE VALUE STORED IN A CELL. IT KEEPS TRACK
' OF THE SHEET NAME PERMANENTLY AND RESETS IT EVERY TIME IT IS RENAMED. IT
'ASSUMES YOU ALREADY HAVE A SHEET NAMED "MYSHEETNAME", AND THE FIRST SECTION
'THAT CAPTURES THE SHEET NAME TO THE REGISTRY NEED ONLY BE DONE ONCE AND NEEDS TO
'BE DISABLED OR REMOVED AFTER THE FIRST TIME IT IS RUN.

'******THIS PART NEEDS TO BE DONE ONLY ONCE TO SET INITIAL VALUES*****

OldName = "MYSHEETNAME"
'SETS THE INITIAL VALUE OF THE OLD SHEET NAME

SaveSetting "MyName", "MyProject", "OldSheetName", OldName
'Sets the Windows registry to create the appname (MyName), section
' (MyProject), key (OldSheetName) and setting (OldName). It saves the value of OldName
'in the registry variable "OldSheetName" until it is reset via the same command.

'AFTER THIS PART IS DONE ONCE, THEN DISABLE OR REMOVE IT

'*********************************************************

OldName = GetSetting("Myname", "MyProject", "OldSheetName")
'THIS GETS THE OldSheetName VALUE THAT HAS BEEN STORED IN THE WINDOWS REGISTRY
'and sets the OldName variable to that value

NewName = Range("$b$3")
'ASSIGNES SHEETNAME VARIABLE TO CONTENTS OF B3. YOU COULD USE ANY CELL REFERENCE
'OR RANGE NAME HERE YOU WANTED

Worksheets(OldName).Name = NewName
'RENAMES THE PREVIOUS WORKSHEET NAME TO THE NEW NAME

OldName = NewName
'RESETS THE VALUE OF THE OLDNAME VARIABLE TO THE NEW NAME

SaveSetting "MyName", "MyProject", "OldSheetName", OldName
'RESETS THE VALUE OF OLDSHEETNAME TO THE CURRENT SHEET NAME TO STORE IT
'PERMANENTLY IN THE WINDOWS REGISTRY

End Sub