VBA to proctect locked cells in sheet

[swatsp0p]

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

 

[dominicb]

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

 

[Norman Jones]

Hi Swatsp0p,

Try:

ActiveSheet.EnableSelection =xlUnlockedCells

 

[Tom Ogilvy]

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.

 

[swatsp0p]

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]

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.

 

[dominicb]

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]

Tom: Thank you for your explanation of the 'oversight' by MS re: th
non-persistent issue with this code. I have placed this code in th
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. W
all just make the best of it and appreciate the time and effort o
those that care enough to respond.

Thanks again to all!!

Bruc

 

[Tom Ogilvy]

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.