How to Name Worksheet Name with Specified Cell Value in Excel

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.

Name Worksheet Name 1

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.

Name Worksheet Name 2

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.

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

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

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

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

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

Name Worksheet Name 3

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.

Name Worksheet Name 4

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.

Name Worksheet Name 5

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.

Name Worksheet Name 6

Related Posts

Find and Replace Multiple Values

This post will guide you how to find and replace multiple values at once with VBA macro or using formula  in Excel. How do I make multiple find and replace in Excel. Suppose that you have a few cells containing ...

VBA Macro For VLOOKUP From Another Sheet

In the previous post, you should know that how to fix or remove the #N/A error when using VLOOKUP formula to lookup value from another sheet. And this post will show you how to use VBA code to vlookup data ...

How To Insert Comments in Protected Worksheet in Excel

This post will show you how to allow comments in a protected worksheet in Excel. You can easily to insert comments into cells in a normal worksheet in Excel, but if want to insert a comment in a worksheet that ...

How To Convert Text to Upper Cases(Using VBA) in Excel

This post will show you how to switch from lower case to upper case in Excel. and I am going to show you two different ways of converting text to upper cases using formula or VBA macro in Excel 2013,Excel ...

How To Hide Every Other Row in Excel (Using VBA)

This post will show you how to hide alternate rows or columns in Excel or how to hide every third, fourth, fifth row or column in Excel. If you want to hide every other row in your current worksheet, how ...

Sidebar