Morning all
I am trying to construct a workbook without using code.
One of my challenges is to have a cell on a WS become the tab name. I know that I can do it around the other way by using =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) which gives me the sheet tab name in any cell but can it be done the other way around with a formula?
PS: I have my doubts because of the complicated code provided by MVP Tom Urtis here
Cheers
Use name in cell as the WS tab (2007/2010)
-
- SilverLounger
- Posts: 1952
- Joined: 26 Jan 2010, 12:46
- Location: Nr. Heathrow Airport
Use name in cell as the WS tab (2007/2010)
Steve
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Use name in cell as the WS tab (2007/2010)
Hi Steve,
As you have identified, it is not possible to name a worksheet via a formula or any other means except manually or via macro.
The code that Tom posted is complex as it does a lot of validation to ensure the name meets the naming conventions of a sheet tab, but without all that complication, one can do is as follows:
As you have identified, it is not possible to name a worksheet via a formula or any other means except manually or via macro.
The code that Tom posted is complex as it does a lot of validation to ensure the name meets the naming conventions of a sheet tab, but without all that complication, one can do is as follows:
Code: Select all
Sub NameSheet()
On Error Resume Next
ActiveSheet.Name = Range("A1").Value
End Sub
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- SilverLounger
- Posts: 1952
- Joined: 26 Jan 2010, 12:46
- Location: Nr. Heathrow Airport
Re: Use name in cell as the WS tab (2007/2010)
RudiRudi wrote:Hi Steve,
As you have identified, it is not possible to name a worksheet via a formula or any other means except manually or via macro.
The code that Tom posted is complex as it does a lot of validation to ensure the name meets the naming conventions of a sheet tab, but without all that complication, one can do is as follows:
Code: Select all
Sub NameSheet() On Error Resume Next ActiveSheet.Name = Range("A1").Value End Sub
If I do go down the code route this will be very useful, tell me please, is this dynamic or would it automatically update each time the WB was opened
Cheers
Steve
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Use name in cell as the WS tab (2007/2010)
It can be made dynamic as the code can be triggered by event; specifically the Workbook_Open event that can update the tab each time the workbook is opened.
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- SilverLounger
- Posts: 1952
- Joined: 26 Jan 2010, 12:46
- Location: Nr. Heathrow Airport
Re: Use name in cell as the WS tab (2007/2010)
Thanks RudiRudi wrote:It can be made dynamic as the code can be triggered by event; specifically the Workbook_Open event that can update the tab each time the workbook is opened.
I will give that a go
Cheers
Steve
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin