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

VBA Macro For VLOOKUP From Another Sheet
vba macro for vlookup from another sheet1

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
insert commetns in protected workshee3

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
convert text to upper cases1

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)
hide every other row1

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

How to Disable the Save As Prompt in Excel
disable save as prompt1

This post will show you how to use a VBA Macro to save an Excel file and overwrite any existing file without a prompt so that you are going to get the little window that says file already exists do ...

How to Count Cells that Contain even or odd numbers in Excel
count cells that contain even numbers5

This post will guide you how to count the number of cells that contain odd or even numbers within a range of cells using a formula in Excel 2013/2016.How do I count cells that contain odd numbers through the use ...

How to Count Cells that Contain negative Numbers in Excel

This post will guide you how to count the number of cells that contain negative numbers within a range of cells using a formula in Excel 2013/2016.You can count the number of negative numbers in your data using easy functions ...

How to Count Cells Are Not Blank or Empty in Excel
count non blank nonempty cells5

This post will guide you how to count cells that are not blank or empty in a given range cells using a formula in Excel 2013/2016.How do I count the number of cells that are not blank in a particular ...

How to Count Cells Less Than a Specific Value in Excel
count cells lessr than5

This post will guide you how to count the number of cells less than a particular numeric value in a given range cells using a formula in Excel 2013/2016. How do I count cells that are less than a specific ...

How to Count Cells Greater Than a Specific Value in Excel
count cells greater than 5

This post will guide you how to count the number of cells greater than a particular numeric value in a given range cells using a formula in Excel 2013/2016. How do I count cells that are greater than a specific ...

Sidebar