Home > Uncategorized > To protect or not protect

To protect or not protect

A bain of my life are spreadsheets that have inappropriate protection – overwritten formulas / inconsistent formatting / inability to modify the workbook to my own requirements. To this end I have battled with users to adopt a method of constructing workbooks which strikes a balance between overengineering a basic workbook with excessive levels of protection and creating an easy to use spreadsheet which will stand the test of time with users. Constantly turning on and off protection is too labour intensive so users tend not to use protection to the detriment of the long term viability of the spreadsheet.

Simple Spreadsheet

As I recall I didn’t specifically trawl the web for a solution but just happened across a solution while reading a forum or blog. I would credit the person who wrote the VBA code but I honestly don’t know where I got it from. protect-button What is it – an embedded ActiveX control with associated code (for newbies this sounds complex – it isn’t – its basically a button that does something when you press it!) which sits on any worksheet which when ‘presssed/clicked’ activates protection (and the text on the toggle button changes to ‘Unprotect’) which also when ‘untoggled’, unprotects the worksheet and changes the text to ‘Protect’. Why is this useful – it enables the user to quickly toggle protection on an off so in order to make valid changes but also quick add protection to avoid careless mistakes.unprotect-button1 I hope you find it as useful as I have in the various operation and construction of spreadsheets. The files are on the side bar called 001.xls and 002.xlsm (basically – the same file but xl2003 and xl2007 versions)

Enhanced by Zemanta

  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: