VBA to Prevent From Changing Worksheet Name in Microsoft Excel

Sometimes our VBA code or some formulas depend on the Worksheet name. If the name of the worksheet changes the the behavior of the VBA code or formula may change. So it very important to know how to prevent the user from changing the sheet name.

In this article we will learn how to use VBA code in order to keep the same name for a worksheet i.e. if anyone wants to change the name then they are not allowed to do so.

Following is the snapshot of the worksheet contains two sheets; Permanent & Temporary

image 1

 

We need follow the below steps to fix the name of the Permanent sheet to “Permanent” itself.

  • Click on Developer tab
  • From Code group, select Visual Basic

image 2

 

  • Enter the following code in the current worksheet module


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Sheet1.Name <> "Permanent" Then
    Sheet1.Name = "Permanent"
End If
End Sub

image 3

 

  • After copying the code; if we try to change the name of the “Permanent” sheet then excel will not allow us.

How does it work?

Here we are using worksheet_SelectionChange Event. This event triggers when the sheet selection is changed.

In this event we are checking if the name of the sheet is "Permanent" or not. If not, we change the name of the sheet to "Permanent" else nothing happens. Easy peasy!

So yeah guys, this is how you can prevent the sheet name from changing. I hope I was explanatory enough. If you have any doubts, please let me know in the comments section below.

Excel  Download-Sample File

Comments

  1. I'm glad they noted the statement to start with "Private" to limit other open work sheets from interfering. Such as starting the statement with "Public".

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.