0

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.

user2591172
  • 19
  • 8

2 Answers2

0

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 :
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.

Community
  • 1
  • 1
gpalex
  • 826
  • 1
  • 11
  • 27
  • Would you please be more specific where the code should be placed and the final portion of the Private Sub? I cannot get the code to work. – user2591172 Jul 17 '13 at 16:38
0

It seems like you haven't tried much.

You can access a worksheet using its name and the Worksheets collection. You can then use any range to access the cell containing the information you wish to retrieve. Assuming it is in cell B2:

Worksheets("ChangeMyName").Name = Worksheets("SomeOtherSheet").Range("B2")

To make it change as soon as the cell is changed, you will have to implement the Change event and compare the Target cell with the cell you want to look after.

http://msdn.microsoft.com/en-us/library/office/ff839775.aspx

ApplePie
  • 8,814
  • 5
  • 39
  • 60