VBA to proctect locked cells in sheet

**swatsp0p** · 06-24-2005, 12:46 PM

ok, programmers... I'm stumped. Writing code to protect a group of sheets, I cannot figure how to prevent users from "Selecting Locked Cells", which is a choice in the Protect Sheet menu.

According to VBA, the choices for ActiveSheet.PROTECT are:

expression.Protect(Password, DrawingObjects, Contents, Scenarios, UserInterfaceOnly, AllowFormattingCells, AllowFormattingColumns, AllowFormattingRows, AllowInsertingColumns, AllowInsertingRows, AllowInsertingHyperlinks, AllowDeletingColumns, AllowDeletingRows, AllowSorting, AllowFiltering, AllowUsingPivotTables)

Current code looks like this:

Please Login or Register  to view this content.

I am trying to keep the user in ONLY the 'Unlocked' cells within each sheet. After running the above code, the user is free to move to any cell within the sheet, locked or not. Of course the contents are protected from changes, but can still be selected.

Also, when I manually set this option (unchecking "Selecting Locked Cells"), save and close the file, when re-opened the protection is in place, yet all cells can once again be selected.

Ideas, anyone?

Thanks...

Bruce

**dominicb** · 06-24-2005, 01:08 PM

Good evening SwatspOp

You need this line after your Protect statement:

ActiveSheet.EnableSelection = xlUnlockedCells

This should allow users to flip between unlocked cells only and not select any locked ones. I think that this feature is only available from XL 2002 onwards.

HTH

DominicB

**swatsp0p** · 06-24-2005, 02:03 PM

Hi, dominicb. Thanks, as that is exactly the statement I was looking for. It does what I am looking for.

However, I still find that after I run this code, save the workbook (all protected), close and then reopen, the protection is still in place, yet the ActiveSheet.EnableSelection = xlUnlockedCells is NOT in place...the user is able to select all cells.

I know I have other sheets that I have manually protected this way and they keep this protection after closing and reopening.

Any more ideas? (for now, I have placed the PROTECT macro in the workbook OPEN event).

Bruce

Tom Ogilvy · 06-24-2005, 02:05 PM

ActiveSheet.EnableSelection = xlUnlockedCells

this has to be performed each time the workbook is opened. Unlike the
manual setting, this setting is not persistent when performed via code (it
isn't an option in the Protect command). An apparent oversight by
Microsoft.

--
Regards,
Tom Ogilvy

"swatsp0p" <[email protected]> wrote in
message news:[email protected]...
>
> ok, programmers... I'm stumped. Writing code to protect a group of
> sheets, I cannot figure how to prevent users from "Selecting Locked
> Cells", which is a choice in the Protect Sheet menu.
>
> According to VBA, the choices for ActiveSheet.PROTECT are:
>
> -expression.Protect(Password, DrawingObjects, Contents, Scenarios,
> UserInterfaceOnly, AllowFormattingCells, AllowFormattingColumns,
> AllowFormattingRows, AllowInsertingColumns, AllowInsertingRows,
> AllowInsertingHyperlinks, AllowDeletingColumns, AllowDeletingRows,
> AllowSorting, AllowFiltering, AllowUsingPivotTables)-
>
> Current code looks like this:
>
> Code:
> --------------------
>
> Sub PROTECT()
> '
> Sheets("January").Select
> ActiveSheet.PROTECT DrawingObjects:=True, Contents:=True,
Scenarios:=True,_
> AllowFiltering:=True, UserInterfaceOnly:=True
> Sheets("February").Select
> ......
> End Sub
>
> --------------------
>
> I am trying to keep the user in ONLY the 'Unlocked' cells within each
> sheet. After running the above code, the user is free to move to any
> cell within the sheet, locked or not. Of course the contents are
> protected from changes, but can still be selected.
>
> Also, when I manually set this option (unchecking "Selecting Locked
> Cells"), save and close the file, when re-opened the protection is in
> place, yet all cells can once again be selected.
>
> Ideas, anyone?
>
> Thanks...
>
> Bruce
>
>
> --
> swatsp0p
>
>
> ------------------------------------------------------------------------
> swatsp0p's Profile:
http://www.excelforum.com/member.php...o&userid=15101
> View this thread: http://www.excelforum.com/showthread...hreadid=382012
>

Norman Jones · 06-24-2005, 02:05 PM

Hi Swatsp0p,

Try:

ActiveSheet.EnableSelection =xlUnlockedCells

---
Regards,
Norman

"swatsp0p" <[email protected]> wrote in
message news:[email protected]...
>
> ok, programmers... I'm stumped. Writing code to protect a group of
> sheets, I cannot figure how to prevent users from "Selecting Locked
> Cells", which is a choice in the Protect Sheet menu.
>
> According to VBA, the choices for ActiveSheet.PROTECT are:
>
> -expression.Protect(Password, DrawingObjects, Contents, Scenarios,
> UserInterfaceOnly, AllowFormattingCells, AllowFormattingColumns,
> AllowFormattingRows, AllowInsertingColumns, AllowInsertingRows,
> AllowInsertingHyperlinks, AllowDeletingColumns, AllowDeletingRows,
> AllowSorting, AllowFiltering, AllowUsingPivotTables)-
>
> Current code looks like this:
>
> Code:
> --------------------
>
> Sub PROTECT()
> '
> Sheets("January").Select
> ActiveSheet.PROTECT DrawingObjects:=True, Contents:=True,
> Scenarios:=True,_
> AllowFiltering:=True, UserInterfaceOnly:=True
> Sheets("February").Select
> ......
> End Sub
>
> --------------------
>
> I am trying to keep the user in ONLY the 'Unlocked' cells within each
> sheet. After running the above code, the user is free to move to any
> cell within the sheet, locked or not. Of course the contents are
> protected from changes, but can still be selected.
>
> Also, when I manually set this option (unchecking "Selecting Locked
> Cells"), save and close the file, when re-opened the protection is in
> place, yet all cells can once again be selected.
>
> Ideas, anyone?
>
> Thanks...
>
> Bruce
>
>
> --
> swatsp0p
>
>
> ------------------------------------------------------------------------
> swatsp0p's Profile:
> http://www.excelforum.com/member.php...o&userid=15101
> View this thread: http://www.excelforum.com/showthread...hreadid=382012
>

Tom Ogilvy · 06-24-2005, 03:05 PM

Activesheet.EnableSelection = xlUnlockedCells has been available since xl97,
not since xl2002

As stated in my answer, this setting is non persistent so it has to be set
each time a workbook is open.

Perhaps you should read all answers before responding.

--
Regards,
Tom Ogilvy

"swatsp0p" <[email protected]> wrote in
message news:[email protected]...
>
> Hi, dominicb. Thanks, as that is exactly the statement I was looking
> for. It does what I am looking for.
>
> However, I still find that after I run this code, save the workbook
> (all protected), close and then reopen, the protection is still in
> place, yet the ActiveSheet.EnableSelection = xlUnlockedCells is NOT in
> place...the user is able to select all cells.
>
> I know I have other sheets that I have manually protected this way and
> they keep this protection after closing and reopening.
>
> Any more ideas? (for now, I have placed the PROTECT macro in the
> workbook OPEN event).
>
> Bruce
>
>
> --
> swatsp0p
>
>
> ------------------------------------------------------------------------
> swatsp0p's Profile:
http://www.excelforum.com/member.php...o&userid=15101
> View this thread: http://www.excelforum.com/showthread...hreadid=382012
>

**dominicb** · 06-24-2005, 04:15 PM

Tom

Thanks for your note re XL97 - I stand corrected.

BTW, your first response to Bruce was timed at 1.05, Bruces response to my post timed at 1.03, ie., he couldn't have read your answer before posting his response. Bear in mind that your post on the Microsoft newsgroups filters to other help sites (such as this - which updates at 5 minutes past the hour) and such a delay may account for this.

DominicB

**swatsp0p** · 06-27-2005, 08:55 AM

Tom: Thank you for your explanation of the 'oversight' by MS re: the non-persistent issue with this code. I have placed this code in the Workbook Open event and all is well.

Thanks to all who reponded with tips to solve my dilema. And, yes, sometimes the time lag from submission to posting is frustrating. We all just make the best of it and appreciate the time and effort of those that care enough to respond.

Thanks again to all!!

Bruce

Tom Ogilvy · 06-27-2005, 04:05 PM

I see my original post at 1:16 and the post by "Bruce" at 2:03 (both PM -
my local time). In newsgroups, usually a 45+ minute elapsed time is safe
to assume propogation although not always - guess the Excel forum is a bit
slower. My opologies to the OP.

--
Regards,
Tom Ogilvy

"dominicb" <[email protected]> wrote in
message news:[email protected]...
>
> Tom
>
> Thanks for your note re XL97 - I stand corrected.
>
> BTW, your first response to Bruce was timed at 1.05, Bruces response to
> my post timed at 1.03, ie., he couldn't have read your answer before
> posting his response. Bear in mind that your post on the Microsoft
> newsgroups filters to other help sites (such as this - which updates at
> 5 minutes past the hour) and such a delay may account for this.
>
> DominicB
>
>
> --
> dominicb
> ------------------------------------------------------------------------
> dominicb's Profile:
http://www.excelforum.com/member.php...o&userid=18932
> View this thread: http://www.excelforum.com/showthread...hreadid=382012
>