How to get a sheet name to immediately update to information found in a cell on another sheet on Excel 2007

Question

I would like to update my sheet name with information from a cell from another worksheet in Excel 2007. I only want to change specific worksheet names (not all worksheets within a workbook) and the information for these worksheets will come from cells within the same worksheet. I would like these changes to the sheet name to be made as soon as the cell is changed.

Thanks.

gpalex gpalex · Accepted Answer · 2013-07-17T11:50:35

You want to use something like

Sub changeName()

   ThisWorkbook.Sheets("nameToBeChanged").Name = ThisWorkbook.Sheets("secondSheetName").Range("A1").Value

End Sub

This will use the value of the cell A1 from the sheet secondSheetName as the name of the sheet called nameToBeChanged.

Don't forget that the following characters are not allowed in the name of a sheet (i.e. the cell A1 mustn't contain the following characters) :

\ / ? * [ ] < > " | :

The maximum length for the name of a sheet is 31 characters

And then to do that as soon as the value of the cell is changed, use events. You can start with this code :

Private Sub Worksheet_Change(ByVal Target As Range)

    MsgBox "Value changed for cell: " & Target.Address        
    Call changeName

End Sub

You need to place that code in the sheet (inside the VBA window of course) where you want the events to be listened.

You might find that post from Santosh usefull

EDIT: here is the picture from Santosh (see the link above). It shows you exactly where to place the code :

As you can see, the code is placed in the item named Sheet1 (where the arrow is pointing from). In this case since the code is placed in the Sheet1, every cell modified will call the sub Worksheet_Change.

How to get a sheet name to immediately update to information found in a cell on another sheet on Excel 2007

2 Answers