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?

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

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.

Geoff wrote:
>
> 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?
>
> --
> There are 10 types of people in the world - those who understand binary and
> those who don't.

--

Dave Peterson

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
--
There are 10 types of people in the world - those who understand binary and
those who don't.


"Dave Peterson" wrote:

> 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.
>
> Geoff wrote:
> >
> > 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?
> >
> > --
> > There are 10 types of people in the world - those who understand binary and
> > those who don't.
>
> --
>
> Dave Peterson
>