Lock all non-empty cells

**knab** · 02-24-2010, 08:04 AM

Hi.
Here is my situation.
I have a workbook with some sheets (sample as a attachment).
I want all the cells that aren't empty (!= "") to be Locked, and all other UnLocked, so when I protect it only unlocked cells could be modified.

Another solution that is 100% satisfying (and which I've been trying to figure out) is to Lock a minimum rectangle that covers all the non-empty cells (in the attached .xls coloured with yellow).

So far I've written this:

Please Login or Register  to view this content.

First Loop DOES work, but the second Locks more than needed (sometimes) and It's the main problem I've been experiencing.

Please Help, thanks in advance.
Mike

**contaminated** · 02-24-2010, 08:14 AM

Try it out

Please Login or Register  to view this content.

**knab** · 02-24-2010, 08:24 AM

Hi, thanks for reply but this doesnt work. On my real workbook i have 2 sheets.
On first, your sub locks empty cells and unlocks not empty ones,
On second sheet it does nothing (all cells are locked).

**contaminated** · 02-24-2010, 08:28 AM

I have edited my previous post.
Please take a look again...

**knab** · 02-24-2010, 08:40 AM

Again, it doesn't work. This time it blocks some empty cells, and others not.
On another sheet it blocks everything, despite being empty or not.

I've attached current .xls that I tried your sub on.

**contaminated** · 02-24-2010, 09:04 AM

Hi
I apologise for misunderstanding first. Below code Protects all non empty cells and unblocks all blank cells
First unprotect your sheets then run this code, else u'll get Unable to set lock property of range class error.

Please Login or Register  to view this content.

**knab** · 02-24-2010, 09:33 AM

Yes it works. But What about my 2nd solution that I was working on ? (lock smallest rectangle that covers the nonempty cells?) In some cases this would be more useful than the one proposed by you (working really good right now:P).