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
You can download an example workbook with the code here:
We ask for your email address so we can send you any updates to the code. We do not ever share emails addresses and take privacy very seriously.
Thanks,
Ray Mills
If you would like to say hello and Buy me a cup of coffee please follow the link.
