Use name in cell as the WS tab (2007/2010)

[steveh]

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

[Rudi]

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

[steveh]

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

Rudi

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

[Rudi]

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.

[steveh]

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.

Thanks Rudi

I will give that a go

Cheers