Name the tab as the contents of a cell in Excel

Question

How do I put the tab name equal to the contents of a cell? For example, if cell content A1 for 01, the name of the guide is 01 also.

Answer 1

You will need to use VBA to do what you want.

My first attempt was to create a function that you could use directly in the formula. But that didn’t work (it just didn’t update the property Name tab, probably because Excel was calculating formulas at that time).

The second attempt worked, based on the event of changing the selection of cells in the spreadsheet. Just open the VBA editor and enter the following code:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Set Target = Range("A1")
    If Target = "" Then Exit Sub
    On Error GoTo Badname
    ActiveSheet.Name = Left(Target, 31)
    Exit Sub
Badname:
    MsgBox "Por favor, revise o nome na célula A1." & Chr(13) _
    & "Parece que ele contém um ou mais caracteres ilegais." & Chr(13)
    Range("A1").Activate
End Sub

Note that this code assumes that the user will be typing the value of this cell, and therefore displays an error message in case of problems in updating the tab name. If your cell is automatically updated (from another source other than the user), you probably want to remove this message view and replace it with an automated handling (maybe set a default title, for example).

Don’t forget to save your macros-enabled spreadsheet ( *.xlsm extension).

P.S.: I was about to make a code when I found this one ready (I just translated the text). The source is this site.