When you write macros or VBA scripts in Excel for a big user base you often have to come up with ways to prevent them from making changes that will cause your scripts to error, one of these is when users change worksheet names meaning your VBA will no longer be able to find what it is looking for, read on to find out how to prevent this…
Locking down your Excel file can be done in several ways, the main method is to protect the workbook or the workbook structure using a password but sometimes that is too extreme for the task, perhaps you need people to be able to edit and change certain aspects of the Excel file.
One of the main issues that I have come across is when users can change worksheet names, this has major implications when you have created a macro or VBA script that they are using. For example if your script refers to cell D12 on the “Data” worksheet and the user decides to rename the worksheet to “MyData” then you will soon get an email in your Inbox telling you that your fancy VBA is no longer working!
How do we fix this?
The solution to this problem is relatively simple, you need very limited knowledge of VBA to implement this, in fact with the solution provided you can copy and paste directly into Excel and with a couple of tweaks it is ready to go.
- Right-click on the Worksheet tab that you want to set:
2) In the Option box that appears left-click on “View Code”
3) Paste in the following code into the code window:
1 2 3 4 5
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) If ActiveSheet.Name <> "Data" Then ActiveSheet.Name = "Data" End If End Sub
Note: For your own project the only part of this code that needs alteration is the sheet name, in my example it is called “Data” so just change that to whatever your requirements are.
4) Save your workbook and that’s it, the worksheet name has been set and is no longer changeable.
What have we done?
The script is specific to the worksheet you have selected in part 1. You right-click the worksheet name and enter the code specifically for that sheet. So if you want to implement this for multiple sheets then just repeat the process with the sheets in question.
The VBA script is basically forming a workaround, it will allow the user to rename the worksheet but as soon as they do it automatically reverts it back to what you have set in the VBA therefore it can never be changed.
The hardcore Excel users among my readers may think that this is still not fully preventing a change because there is nothing to stop your users going into VBA and deleting the VBA script. While that is true I generally find that if your users have enough experience to delete VBA scripts then they probably know better than to do so, however we can take it a stage further and lock down all the VBA.
If you want to lock down the VBA and make this change 100% effective then read my previous post here on how to protect your VBA code in Excel.