Still can select locked cells in protected sheet

[Andy]

When I protect a sheet, and the only action allowed is "Select
unlocked cells", the user cannot select any locked cells, which is
what I want.

When I save the sheet, and then reopen it, the user can select locked
cells. It doesn't do him any good, since he can't alter the cell. If
he tries, he gets an error message advising him that the cell is
protected.

If I unprotect and re-protect the sheet, the behavior goes back to not
even being able to select the locked cells.

Is there a way to ensure that behavior when the locked worksheet is
re-opened?

TIA,

Andy

 

[Anne Troy]

Andy, I'm afraid that the way you worded it, I (for one) am not clear on
exactly what you want to accomplish. You want the user to be able to select
cells but not change them? Or not to even select the cells? And what version
are you using, please?
************
Anne Troy
www.OfficeArticles.com

 

[Ron de Bruin]

Hi andy

If the user is using Excel 2000 this is not working
This was added in 2002

 

[Andy]

Anne,

I'm using Excel 2002, and the following macro does what *I* want,
which is to not even be able to select the locked cells:

Sub Workbook_open()
'put this in This Workbook
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
sh.Protect
sh.EnableSelection = xlUnlockedCells
Next sh
End Sub

Without the macro, since I saved the workbook with all the sheets
protected, when I reopen the file I still can't alter the locked
cells, but I can select locked cells. It's not that big a deal but
it's aesthetically less pleasing, since I can click on a protected cell
and see the heavy active cell border, and trigger the error message if
I try to enter something in the protected cell. None of this
behavior is possible if I re-protect the sheets.

I'm fine with my fix above. The problem is that I want to share the
template, and I assume the Workbook_open macro will trigger other
user's macro security warnings.


Andy

 

[Andy]

Ron,

See my reply to Anne.

I think one of the users I want to share the workbook with is using an
older version of Excel than either 2002 or 2000.

I'll be on the lookout for problems...


Andy


PS I can't believe a bare TEN MINUTES after I pose my question I have
two responses. I love you guys!!!

 

[Andy]

Ron,

What is it that doesn't work in Excel 2000 (I have 2002), that cells I
lock can be changed by a 2000 user even though I protected the
worksheet?

Andy

 

[Anne Troy]

Hate to ask, Andy, but are you sure you're locking the cells properly?
See: http://www.officearticles.com/excel/cell_locking_in_microsoft_excel.htm
************
Anne Troy
www.OfficeArticles.com

 

[Ron de Bruin]

Hi Andy

The option to do this when you manual protect your sheet is not in 97-2000.
So if you manual protect your sheet with this setting in 2002 and open it in 2000 it is not working

 

[Andy]

are you sure you're locking the cells properly?

I think so Anne. On the Protection tab of the Format Cells dialog box
I have "Locked" checked on all of the cells I don't want the user to
be able to access.

Then I go to Tools>Protection>ProtectSheet and the only box checked on
the Protect Sheet dialog box is "Select unlocked cells".

When I do this (Excel 2002), everything works as expected.

But when I save and reopen the file, I can select the locked cells
with the mouse. I can't do anything with the selected cells, so the
sheet is still protected and only the unlocked cells are truly
available. But I wonder why the behavior changes when I reopen the
file.


Andy

 

[Anne Troy]

Andy: When I test this in 2003, I cannot select cells.
When I test in 2000, I can still select cells.
Are you sure you're using the same version always? I have 4 versions on my
PC. Perhaps Excel 2000 is your default, and you're double-clicking? Gotta
ask... This behavior is just bizarre. Perhaps you should try troubleshooting
Excel?
http://www.officearticles.com/excel/troubleshooting_microsoft_excel.htm
************
Anne Troy
www.OfficeArticles.com

 

[Andy]

The option to do this when you manual protect your sheet is not in

97-2000. So if you manual protect your sheet with this setting in
2002 and open it in 2000 it is not working

Hi Ron,

Ok, if I understand you right, in 97-2000 I can protect the cell but
when the sheet is locked the user will still be able to select the
cell even though they won't be able to alter it.

Still doesn't explain why my 2002 is allowing me to select the cell,
but I'm ok with my workbook_open workaround.

Thanks,

Andy

 

[Andy]

Hi Anne,

I only have 2002 on my machine, so I guess the behavior is as you say
bizarre.

I'm ok with my workbook_open workaround, and unless something else
becomes screwy, I'm going to put off the troubleshooting project (but
I'm keeping the link!).

Thanks,

Andy