When we launching Excel and create a new workbook, we can find that some worksheets are already created and the default sheet name is sheet1 for example. Actually, we have to rename worksheet name in most situations depends our requirement. And in our daily life we often rename worksheet name by right click or double click on sheet1 to edit a new sheet name. But sometimes we want to rename it by a specific value in some situations, for example base on one specified cell value in excel, how can we do? I think this article will help you, it will introduce the method for updating worksheet name with cell value by VBA code.

Name Worksheet Name with Specified Cell Value in Excel

---

Precondition:

Launch excel and create a table in sheet1 for example a table of score summary.

Step 1: On current visible worksheet, right click on sheet name tab to load Sheet management menu. Select View Code, Microsoft Visual Basic for Applications window pops up. Or you can enter Microsoft Visual Basic for Applications window via Developer->Visual Basic.

Step 2: In Microsoft Visual Basic for Applications window, enter below code.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Set Target = Range("A1")

If Target.Address <> "$A$1" Then Exit Sub

If Target = "" Then Exit Sub

If Len(Target.Value) > 31 Then MsgBox "You cannot enter tab name greater than 31 characters in length!"

Application.ActiveSheet.Name = VBA.Left(Target, 31)

Exit Sub

End Sub

Comments:

1. Set Target = Range(“A1”)

We use A1 value as new sheet name; you can change it to another cell depends on your settings.

2. If Target.Address <> “$A$1” Then Exit Sub

If user click on another cell, sheet name cannot be changed.

3. If Target.Address <> “$A$1” Then Exit Sub

If we leave A1 as blank, sheet name cannot be changed.

4. If Len(Target.Value) > 31 Then MsgBox “You cannot enter tab name greater than 31 characters in length!”

The default max length for sheet name is 31 characters, so you cannot enter a name greater than 31 characters. Otherwise error message will pop up.

Step 3: Save the codes, see screenshot below. And then quit Microsoft Visual Basic for Applications.

Step 4: Click on A1. Verify that sheet name is updated with A1 value automatically. That means VBA code takes into effective. It applies on A1 properly.

Notes:

1.Remove all characters from A1 and keep it as blank cell. Verify that sheet name is not updated.

2.Click on other cells for example B1. Verify that sheet name is not updated.

3.Type a long string (>31 characters) into A1. Verify that below error message pops up, the long string will be truncated, and only the first 31 characters are saved as sheet name after clicking on OK.

4. Enter some special characters into A1 like slash or question mark, below error message pops up. You can see that below special characters are not supported. The previous sheet name will not be updated.