Show Buttons
Share On Facebook
Share On Twitter
Share On Google Plus
Share On Linkdin
Share On Reddit
Contact us
Hide Buttons

Excel-VBA : Prevent Changing the WorkSheet Name

Many a times you need a sce­nario where you don’t want user to change the work­sheet name in you Excel work book.

Its quite easy to do.

Steps:

    • Go to VBA Editor
    • Expand your VBA Project
    • Select the any Sheet

Select WorkSheet

Select Work­Sheet

  • Now select the “Work­Sheet” from the first drop down on the right
Select WorkSheet from dropdown

Select Work­Sheet from dropdown

  • Select the Selec­tion­Change from the sec­ond drop down on the right
Select SelectionChange

Select Selec­tion­Change

  • Now you will see the func­tion Pri­vate Sub Worksheet_SelectionChange(ByVal Tar­get As Range)
  • Add the fol­low­ing code in the function.
If Sheet9.Name <> "FixedName" Then

   Sheet9.Name = "FixedName"

End If
Code

Code

And thats it you are done. Lets test our program.

Go to excel and try chang­ing the work­sheet name.

Prevent changing the sheet name

Pre­vent chang­ing the sheet name

 

You may also like...

3 Responses

  1. Michael Akinya says:

    Hi, thanks for this code… seen it else­where as well. I’m try­ing to utilise it for a project, but the prob­lem is that it doesn’t work. I found an arti­cle from cpear­son that maybe explains why it isn’t work­ing (http://www.cpearson.com/excel/RenameProblems.aspx) » “When a user renames a worskheet no event pro­ce­dure is trig­gered, so there is no event to trap the renam­ing, let alone can­celling the operation”

    Could you please shed more light to why it would not work as the code lines inside the pro­ce­dure shell is sim­ple enough… The pro­ce­dure shell was auto gen­er­ated, as I was in the rel­e­vant work­sheet module.

Leave a Reply

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

%d bloggers like this: