Lock and Unlock Sheets based on Cell values

[Mujahid_Sgd]

Hi All,

I have an excel workbook in which there are three sheets, each sheet has two cells which I have named as Sheet1!Sign1, Sheet1!Sign2.... Sheet3!Sign2.

What I intend to do is to lock the sheet whenever the signature cells are filled in for a particular sheet.

Please Login or Register  to view this content.

The overall intention is that when someone fills in the signature cells the sheet gets locked or protected and whenever the signs are deleted only then data can be edited

With the code above the sheet gets protected but I want to leave the sign cells unprotected.

Two commented lines in the code return an error

Unable to set the locked property of range class to locked

Can someone please help

Thanks

Mujahid

[DonkeyOte]

If you need to be able to clear Sign1 & Sign2 then those cells must always remain Unlocked irrespective of sheet protection - on which basis perhaps you want something like:

Please Login or Register  to view this content.

so the above will always leave Sign1 & Sign2 unlocked and will protect the sheet only where both Sign1 & Sign2 have been populated.

modify variables / references as necessary

[Mujahid_Sgd]

Thanks for the reply..
and thanks for the help.. the code works beautifully..

Just wondering if this code can be extended to n number of sheets.

[Mujahid_Sgd]

I tried this and it seems to be working

Please Login or Register  to view this content.

[DonkeyOte]

Just wondering if this code can be extended to n number of sheets.

if "n" is determined by simply however many sheets have both Sign1 & Sign2 ranges then it might be simplest to make this requirement the pre-emptive test:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

[Mujahid_Sgd]

Can the same functionality by put in an add in so that I donot have to write the code behind everywork book.
I actually tried saving the book as an add in and then enabled that add in but it does not work even if I change the function type to public and define Sign1 and Sign2 in the other workbook

What am I doing wrong?