How do you trap the worksheet unprotect?

[Dreiding]

I needs to detect if a workbook has had any worksheets unprotected.
My expectation is to have a document property set to "unchanged" with
worksheets protected when saved. I then send out the workbook to my
customers. If they unprotect any worksheet, the document property gets set to
"modified". This way I can detect if they potentially modified some of the
equations, etc..

Any suggestions?

Thanks,
- Pat

 

[Jim Thomlinson]

Unprotecting a sheet is not an event that is captured in XL. If I were doing
it I would do the following. On each sheet create a named range which
includes all of the cells that you do not want modified. Make it a local
named range (specific to the sheet and not the entire workbook) and use the
same name like "Formulas" on each sheet.

Now you can use the SheetChange event in this workbook and see if the target
intersects with the named range. If it does then you know one or more of your
formulas have been modified.

 

[Dreiding]

To bad "unprotecting" can't be trapped. Your suggestion is a challenge for
me to implement. Is there a way I can select all the "locked" cells and make
than all part of a named range? Can this be done at the workbook level (one
range for all locked cells in the workbook?). I'll accept something by
worksheet if I can.

Thanks,
- Pat

 

[Dreiding]

The solution I choose is based on the cells protected property. I set all
the cells to be protected except those I all the user to edit. If one of the
modified cells has the protected property set, I know the user unprotected
the worksheet. Easy to implement!

Thanks for help!
- Pat