Custom Excel Worksheet Protection

Custom Excel Worksheet Protection

The scene was repeated far too many times: We sent out our Excel forecast single worksheet workbooks to the various divisions with specific instructions:  Do not change anything, do not add rows or columns.  Just input your forecast.  Simple right?  Invariably about 10PM when we ran our consolidation it failed.

Why? because a division controller decided he or she did not like our format.  Those were the nights that I learned that I did not have the ability to cast evil spells on people. Well, I could cast them but they didnt work. Also,at that time Excel’s functionality around protecting cells was limited and I could not write VBA.  Thankfully, things have changed.

The Clients request was simple “I don’t want the user to change the heading and screw things up”.  I immediately thought “easy peasy”.  Then the client said, “I don’t want to use protection we found that to be a bit clunky”.

My solution was to use the change Event as follows:
1. Create a range object called rProt
2. Set the range of that range object equal to the areas I want to protect
3. Use the intersect function to test if the range changed (target is the range object returned by the change function) overlaps the rPro range.
4. If it overlaps pop up a message 
see the code below

1 Private Sub Worksheet_Change(ByVal Target As Range)
2
3 Dim rProt As Range
4 Set rProt = Range("$A$1:$L$5,$A$6:$B$36,$C$37:$K$37,$K$6:$K$36,$F$6:$F$36")
5
6 If Intersect(Target, rProt) Is Nothing Then
7 ' does not intersect ...
8
9 Else
10 ' change in range ...
11 MsgBox "Changes to the report are limited to Haul columns. " & _
12 This is done so tha data can be programatically consolidated., vbCritical, "Report may not be altered"
13
14 ' turn off the change event incase there are dependencies ...
15 Application.EnableEvents = False
16
17 ' undo the change made ...
18 Application.Undo
19
20 ' turn events back on ....
21 Application.EnableEvents = True
22 End If
23
24 End Sub

Code Snippets by Ray Mills
This is my solution - Always thoroughly test and retest your code

Custom Excel Worksheet Protection

Custom Excel Worksheet Protection using  the Worksheet change event.

If you enjoyed my blog and would like to comment or share a similar experience please send your comments  Here.
If you would like to say hello and Buy me a cup of coffee please follow the link.
Raymond Mills MBA, MS
Raymond Mills, M.B.A., M.S.  has spent over 20 years of his career as Accountant, Investment Bank and Credit Card Technical Auditor/ Data Analyst.  His specialty was using Excel to get Big Databases including Teradata, Oracle,  Squel Server and Sybase to give up their secrets. Ray has said “I love nothing better than using VBA to unleash the power of Microsoft Office.” You can contact Ray @ 484 574-3190 or by emailing him Here

If you have a challenge with Excel, Access or Word and would like to speak with Ray,   You can get his contact details by clicking here: Contact Me
Tagged , , , . Bookmark the permalink.

Comments are closed.