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