Archive for the ‘Worksheet Protection’ Category

Automatically Unprotect Sheets Based on Domain Name

April 13th, 2011

POLO (one of my 12 fans) asks a question:

“I want to control read and write access to an Excel file using either windows logins or a list in Excel. Like match userid to the list in Excel and then give either write or read only access to the spreadsheet. Is it possible?”

The answer is yes. You can capture a user’s Windows Domain\UserName (see my previous post on this) and throw that against a list in Excel to determine permissions and views.
» More: Automatically Unprotect Sheets Based on Domain Name

4 Comments »

Posted in Excel VBA, Worksheet Protection

Tags:

Worksheet Protection Best Practice

August 26th, 2010

I was reviewing someone’s workbook the other day way I saw some code similar to this:

Sheets(“Sheet1″).Unprotect Password:=”MeatFace”
PROCEDURE
Sheets(“Sheet1).Protect Password:=” MeatFace “

This code unprotects the sheet, runs a procedure, and then protects the sheet again.  The reason this guy wrapped his procedure with Unprotect/Protect is because his procedure changes a worksheet in some way. And you cannot run a macro that changes a worksheet while that worksheet is protected.

  » More: Worksheet Protection Best Practice

6 Comments »

Posted in Excel Tips and Tricks, Excel VBA, Worksheet Protection

Tags:

Hack into a protected Excel 2007 Sheet

September 18th, 2009

When Microsoft introduced Excel 2007, they introduced new file types – we all know them by now: xlsx, xlsm, xltx, etc. These file types are often referred to as Open XML. That’s because the new file types are essentially packages that contain XML files. If you take an xlsx file and change the extension to zip, you’ll be able to see all the xml documents that make up your Excel file.

 

The new Open XML file types come with lots of benefits. One of the major benefits is that you can change the content and properties of an Excel 2007 file simply by manipulating the XML documents that make it up.

 

Well, while playing with the Open XML files, I discovered that you can remove spreadsheet protection simply by applying a simple edit to the xml within the Excel file.

  » More: Hack into a protected Excel 2007 Sheet

63 Comments »

Posted in Worksheet Protection

Tags:

Protect Worksheet Structure with Array Formulas

July 31st, 2009

In the another display of passive aggressive behavior, I want to show you a trick you can use to prevent anyone from adding or deleting rows or columns by using a simple array formula. 

I’m pretty sure I got this trick from Bob Umlas.  Most Bob tricks, they are extremely cool, but I never seem to find situations where I can use them.  But, maybe this trick will hit the spot for some of you.

. » More: Protect Worksheet Structure with Array Formulas

Leave your comment »

Posted in Excel Formulas, Excel Tips and Tricks, Worksheet Protection

Tags:

Limiting Movement within Your Worksheet

July 14th, 2009

It’s always fun to be passive aggressive toward your users.

Here’s a new way to exert your impotent rage on the power user who’s always doing wonky things to your spreadsheets.

You can limit navigation to a certain range of cells. Here’s how:

  » More: Limiting Movement within Your Worksheet

8 Comments »

Posted in Excel Tips and Tricks, Worksheet Protection

Tags: