VBA & Macro Problems

Automatically Rename Excel Worksheets from Cell B2 Using VBA

Steven ParkerSteven Parker Nov 13, 2026 869 views

Question details

The user wants to automatically rename each worksheet when the value in cell B2 changes, using a Workbook_SheetChange event while excluding specified sheets and handling invalid, duplicate, or overly long sheet names.

Automatically Rename Excel Worksheets from Cell B2 Using VBA
Product
WPS Office Spreadsheet / Microsoft Excel
Device & OS
not provided
Scenario
A workbook has multiple sheets, and the user wants each sheet tab to reflect the value typed in its B2 cell without manually renaming it.
Observed behavior
The expected goal is that any change to cell B2 immediately renames the active worksheet, except for the excluded sheets, and that invalid, duplicate, or overly long names are blocked with a warning.
Before you start

Open the workbook where you want the automatic rename behavior and make sure cell B2 contains a valid test value. Confirm that the VBA/macro editor is available, and after adding the code save the file in a macro-enabled workbook format if prompted.

Solution 1Recommended

Add a Workbook_SheetChange Event in the ThisWorkbook Module

Use this solution to automatically rename any worksheet from its B2 value while skipping listed sheets and warning when a name is invalid, duplicate, or too long.

This event runs whenever any cell on any sheet changes. The code first exits for excluded sheets and for any change outside B2, so it only reacts to edits in B2.

Paste the following VBA procedure into the workbook-level ThisWorkbook module:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Sh.Name = "Sheet1" Or Sh.Name = "Sheet2" Then Exit Sub If Intersect(Target, Sh.Range("B2")) Is Nothing Then Exit Sub On Error GoTo InvalidName Application.EnableEvents = False If Len(Trim$(Sh.Range("B2").Value)) > 0 Then Sh.Name = Left$(CStr(Sh.Range("B2").Value), 31) End If CleanExit: Application.EnableEvents = True Exit Sub InvalidName: MsgBox "The value in B2 is not a valid or unique sheet name." Resume CleanExit End Sub

Update the excluded sheet names in the first If line. Excel sheet names cannot contain characters such as brackets, slashes, question marks, or asterisks, cannot exceed 31 characters, and must be unique in the workbook.

1
Open the Visual Basic editor

In WPS Office Spreadsheet or Microsoft Excel, open the workbook, display the Developer tab if available, and press Alt+F11 or click Developer > Visual Basic to open the VBA editor.

2
Open the ThisWorkbook module

In the Project Explorer, find the VBAProject for your workbook, expand Microsoft Excel Objects, and double-click ThisWorkbook to open its code window.

3
Paste the event code

Copy the Workbook_SheetChange procedure shown above, paste it into the ThisWorkbook module, and change "Sheet1" and "Sheet2" to the sheets you want to exclude.

4
Save and test

Save the workbook as a macro-enabled file if prompted, return to a non-excluded sheet, type a valid name into B2, and press Enter to see the sheet tab update.

Add a Workbook_SheetChange Event in the ThisWorkbook Module
How the error handling works: If B2 contains an invalid character, duplicates another sheet name, or exceeds 31 characters after truncation, the macro shows a warning and re-enables events without renaming the sheet.
WPS Office Spreadsheet

Automatically Rename Worksheets from Cell B2 with a WPS Office Workbook_SheetChange Event

Add the same Workbook_SheetChange procedure in WPS Office Spreadsheet to rename worksheets from B2 automatically. WPS Office opens Excel-compatible macro workbooks and lets you skip selected sheets while handling invalid or duplicate names.

  1. 1. Download and open WPS Office Spreadsheet: Install WPS Office, open the target workbook in WPS Office Spreadsheet, and confirm that the Developer tab or macro editor is available.
  2. 2. Open the VBA editor: Press Alt+F11 or go to Developer > Visual Basic/VBA Editor in WPS Office Spreadsheet to open the macro editing window.
  3. 3. Insert code in the ThisWorkbook module: In the Project Explorer, expand Microsoft Excel Objects, double-click ThisWorkbook, and paste the Workbook_SheetChange event code.
  4. 4. Save and test the workbook: Save the file in a macro-enabled Excel-compatible format when WPS prompts, then test by entering a valid name in B2 on a non-excluded sheet.
Automatically renames worksheets from B2 using a workbook-level VBA eventKeeps Microsoft Excel .xlsm, .xlsx, and .xls format compatibilityFree, lightweight office suite with a familiar spreadsheet interfaceSupports skipping specific sheets and warning on invalid or duplicate namesSaves macro-enabled Excel-compatible workbooks without Microsoft 365
microsoft office alternative - wps office

Frequently Asked Questions

Why isn't the code renaming all sheets?

The first line excludes Sheet1 and Sheet2. Edit the line in the ThisWorkbook module and list only the sheets you want to skip; changes outside B2 are also ignored by the Intersect check.

What happens if B2 contains an invalid sheet name?

The error handler displays the message "The value in B2 is not a valid or unique sheet name." and exits without renaming. Remove invalid characters or make the name unique in the workbook.

Where should I put the VBA code?

Put it in the ThisWorkbook module, not in a standard module or a specific worksheet module, so the Workbook_SheetChange event fires for the entire workbook.

Will the macro rename a sheet when B2 is blank?

No. The code checks the trimmed B2 value and leaves the sheet name unchanged if B2 is empty or contains only spaces.

Is this compatible with WPS Office?

You can open the workbook in WPS Office Spreadsheet and add the code through the VBA/macro editor. Save the file as a macro-enabled Excel-compatible workbook and check your WPS edition for full VBA support.