Prevent Change of Worksheet Tab Names

How to prevent change of worksheet tab names using protect workbook structure and using worksheet code names.

Watch the video below:

The image below shows how to protect the workbook using a password in the manual mode, First we click on review and then click on’Protect Workbook’ with the structure element checked in the pop-up window.

Prevent Change of Worksheet Tab Names using protect workbook structure manually

The VBA code or macro below shows how to prevent deleting or changing of worksheets tab names:

Private Sub Workbook_Open()
ActiveWorkbook.Protect Password:=”takyar”, Structure:=True
If ActiveSheet.Name = “Start” Then
Expenses.Select
Start.Select

End If
End Sub

You can also notice that we are using the worksheet code names instead of the worksheet tab names to access the worksheets when the workbook opens. The image below shows how to change the worksheet code names:

Prevent Change of Worksheet Tab Names using worksheet code names
Prevent Change of Worksheet Tab Names using worksheet code names

Here’s the complete VBA code for ready reference:

Private Sub cboStart_Change()
If cboStart <> “Choose Sheet” Then
Worksheets(cboStart.Value).Select
End If

cboStart.Value = “Choose Sheet”
End Sub

Private Sub Worksheet_Activate()
Dim sht As Worksheet
Me.cboStart.Clear
For Each sht In ThisWorkbook.Worksheets
Me.cboStart.AddItem sht.Name

Next sht

End Sub

Watch this video on YouTube.

Further Reading:

Worksheet.Codename Property