Cells locking after Protect/Unprotect

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Geoff - 24 Aug 2007 03:26 GMT

I have a workbook with an 'entry' sheet for user input, which is protected.
Several cells on the sheet are unlocked to allow user input, the rest have
been locked.

I also have several macros which change various parts of the sheet based on
user input, and so in order to do this they call the Worksheet.Unprotect
method, do their thing, and then call Worksheet.Protect. Some also need to
call the Unprotect/Protect methods of the active workbook, in order to hide
and unhide other sheets.

The problem I am having is that some of the cells which are unlocked for
user input are suddenly locking for no apparent reason, requiring this option
to be changed manually, which some users (and me) find somewhat frustrating.
I am also worried by the fact that this means the sheet has to be manually
unprotected and protected, leaving it open to the type of security issue
protection was put in to avoid. Does anyone know of any reason why this may
be happening? I'm thinking that the difference between the VBA Protect method
and Excel's Protect command may be an issue, but I don't know much about
either.

Any thoughts?

Signature

There are 10 types of people in the world - those who understand binary and
those who don't.

Reply to this Message

Dave Peterson - 24 Aug 2007 13:08 GMT

Just a guess...

I'm guessing that your code clears the cells (somerange.clear, not
somerange.clearcontents). When you .clear a range, the cell is set to the
Normal Style (format|style). And unless you changed something, the default
normal style is to lock the cells.

So you have a couple of choices.

Use .clearcontents (or .value = "") in your code.

Or change the Normal style in the troublesome workbooks.

Format|Style|select Normal from the dropdown.
click modify
Uncheck Locked on the Protection tab

ps.

Styles live in workbooks. You'll have to do this to every workbook you want to
behave this way.

> I have a workbook with an 'entry' sheet for user input, which is protected.
> Several cells on the sheet are unlocked to allow user input, the rest have
[quoted text clipped - 21 lines]
> There are 10 types of people in the world - those who understand binary and
> those who don't.

Signature

Dave Peterson

Reply to this Message

Geoff - 26 Aug 2007 22:20 GMT

Thanks Dave, I'll give that a try - none of the macros use .clear, but I
think one of the users may have been copy/pasting from another spreadsheet
into the affected cells. I'm assuming that would have the same net effect, as
(I believe) the formats, including locking the cell, will be pasted in as
well. I'll tell them to try paste special instead, see if that fixes it.

Cheers

Signature

There are 10 types of people in the world - those who understand binary and
those who don't.

> Just a guess...
>
[quoted text clipped - 43 lines]
> > There are 10 types of people in the world - those who understand binary and
> > those who don't.

Reply to this Message