No one has solved the -Protection-ProtectSheet-SelectLocked

DetroitDr

New Member
Joined
Sep 27, 2006
Messages
9
Ok I've spent over 2 hours reading MANY peoples issues with this "BUG". Back in 2003 was the earliest... no one has an answer that works.

When you protect a worksheet and only have UNLOCKED CELLS selected...somehow through loading and unloading the file... you can select locked cells....
I can not find a pattern but many people have had this issue all with no concrete answers.

Just by loading and saving, exit and loading and saving, exit and loading.... i can now select locked cells that I previously couldn't. (I can't do anything as it's still protected...) It's a pain because it wrecks my tab flow.

Any insight on this BUG?

George
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

DetroitDr

New Member
Joined
Sep 27, 2006
Messages
9
Just a followup

My file does run a macro to turn on/off the protecting for updating values in protected fields... but from what I can tell this is not causing the problem...(but then again I'm not an expert)

Here is my code for protecting
Dim Password
Password = "compnant"
Sheets("Children").Select
ActiveSheet.Protect Password, True, True, True, AllowFiltering:=True
Sheets("Sign In").Select
ActiveSheet.Protect Password, True, True, True, AllowFiltering:=True
 

DetroitDr

New Member
Joined
Sep 27, 2006
Messages
9
Ok my little bit of return code for all the help I've gotten....

I have found the solution.

It appears there is no attribute to toggle the LOCKED CELL part of protecting a sheet... LET ME CLARIFY.....

I havn't seen a ActiveSheet.DisableSelection = xlLockedCells

So what you have to do is turn it all OFF, and then only enable the UNLOCKED CELLS. Here is a complete code to do that so you don't have to figure anything out.:




Sub Protect()
Dim Password 'This line of code is optional
Password = "1234" 'set your password
Sheets("Children").Select 'select the sheet you want to protect
ActiveSheet.Protect Password, True, True, True
ActiveSheet.EnableSelection = xlNoSelection
ActiveSheet.EnableSelection = xlUnlockedCells
End Sub

I hope this helps others that have this "BUG" feeling on the protection side when using VBA.

cheers
 

Enash Sregor

New Member
Joined
Nov 23, 2006
Messages
6
I have been looking everywhere to solve this problem - I initially thought it was something to do with my macro, which I suppose it does. The macro unprotects and protects the sheet, which is where I think the problem lies - somehow this makes the protect function default the 'Select Locked Cells' and 'Select Unlocked Cells' boxes with to be ticked when saving the sheet.

This does not become apparent until you close the prog and reload it again!

Anyway, I tried the code but it didn't work - very sad - I thought this was the answer to a problem that makes my sheet vulnerable because by making a locked cell selectable it means that people can accidentally copy/paste the locked cells (and formula/formatting) to parts of the sheet that then render the sheet useless.

If there is an answer out thee somewhere can someone please let me know!
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832

ADVERTISEMENT

Hello, Enash Sregor
Welcome to the Board !!!!!

You are confusing two different things.
The fact that a cell can be selected, doesn't mean it can be changed.

When a cell is locked - some call it "protected" - this has no effect unless the worksheet is protected.

Also a TIP to consider: don't tell us "code doesn't work": you can clearly see it DOES WORK :biggrin:
I have found the solution.
Only thing you can say, is it doesn't work for you. But then you need to tell what you want to achieve and what you mean by "doesn't work".

OK, let's see if we can help you now ;)

First question: what is your goal? Perhaps you told already, but since it appeared you were confusing some elements, I would prefer, you explained again.

kind regards,
Erik
 

Enash Sregor

New Member
Joined
Nov 23, 2006
Messages
6
Hi Erik,

Thanks for the warm welcome, there was no confusion but I'll explain that at the end!

I use Excel 2003 and this was my problem:

Say I have cell 'A1' on a sheet.

1. I format the cell as 'locked'

2. I password protect the sheet and ensure the that only box that is ticked is the 'Select Unlocked Cells' - the cell is now non-selectable.

3. I save the Workbook, close down the Excel, restart Excel, open the Workbook, and the cell remains non selectable (as it should be)

4. I run a macro (the code is absolutely fine) it copies a range of unlocked cells from one part of my sheet to another range of unlocked cells without any interference to cell 'A1'

5. I save the Workbook, close down Excel, restart Excel, open the Workbook, and the cell is now selectable but it is still protected so it cannot be changed

6. When I unprotect the sheet and then attempt to password protect the sheet - I now note there are two boxes ticked; 'Select Unlocked Cells'; 'Select Locked Cells'.

I can only therefore conclude that something is affecting the default protection settings when a macro of this kind is run, and the first time that the user becomes aware that cell no longer retains the correct cell protection is when the Sheet has been reloaded and the cell clicked upon.

The following solution has now rectified this problem (on my sheet) and it works fine for me:

Code:
Sub Auto_Open()
    Application.ScreenUpdating = False
    Sheets("R1").Select
    ActiveSheet.Unprotect Password:="letmein"
    ActiveSheet.EnableSelection = xlNoSelection
    ActiveSheet.EnableSelection = xlUnlockedCells
    ActiveSheet.Protect Password:="letmein"
    Application.ScreenUpdating = False
End Sub


PS

You are confusing two different things.
The fact that a cell can be selected, doesn't mean it can be changed.

I actually didn't say a selectable 'locked cell' could be changed - what I did say was:

by making a locked cell selectable it means that people can accidentally copy/paste the locked cell (and formula/formatting) to parts of the sheet that will then render the sheet useless

Kind regards

Sandy
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832

ADVERTISEMENT

Sandy,

thank you for the clarification: indeed I misunderstood some elements of your reply
3. I save the Workbook, close down the Excel, restart Excel, open the Workbook, and the cell remains non selectable (as it should be)
Not for me. To my knowledge this is not the normal behaviour.
Normally when you close and reopen, you can select everything again as you have written in point 5.

your code can be written as
Code:
Sub Auto_Open()

    With ActiveSheet
    .Unprotect Password:="letmein"
    .EnableSelection = xlUnlockedCells
    .Protect Password:="letmein"
    End With

End Sub
I do not see the need to change the "ScreenUpdating", although when operating on all sheets (see lower) it is needed.

and this line is not needed
.EnableSelection = xlNoSelection
because you change the setting to unlockedcells

for all sheets

Code:
Sub Auto_Open()
Dim sh As Worksheet

Application.ScreenUpdating = False

    For Each sh In ThisWorkbook.Sheets
        With sh
        .Unprotect Password:="letmein"
        .EnableSelection = xlUnlockedCells
        .Protect Password:="letmein"
        End With
    Next sh

Application.ScreenUpdating = True

End Sub
or in workbookmodule
Code:
Private Sub Workbook_Open()
Dim sh As Worksheet

Application.ScreenUpdating =False

    For Each sh In ThisWorkbook.Sheets
        With sh
        .Unprotect Password:="letmein"
        .EnableSelection = xlUnlockedCells
        .Protect Password:="letmein"
        End With
    Next sh

Application.ScreenUpdating = True

End Sub

best regards
ENJOY the BOARD :)
Erik
 

Enash Sregor

New Member
Joined
Nov 23, 2006
Messages
6
Hi Erik,

Firstly, I used the line:

.EnableSelection = xlNoSelection
because I wasn't sure whether all the settings were being forced to reset before I then set the protection settings for the 'Select Unlocked Cell' parameter - I wanted to ensure a 'belt and braces' job because I'm unable to take anything for granted where Excel is involved!

I previously a similar code to your:

Workbook_Open()

but it didn't work on my system, neither did yours. Maybe it's because I'm placing the macro in the wrong location.

The 'ScreenUpdating' is from the similar code I used to change all my sheets at the same time, initially I didn't want to do this but now I've relented and decided it's the easier option to take.

I am now using the second 'Auto_Open()' you supplied - I'm very surprised that you say it is normal for a sheet which has been protected to only allow selection of 'unlocked' cells - to then reset those protection settings on reload to allow 'locked' cells to then be selected, admittedly they cannot be changed, but surely that defeats the object and intentions of the originator who requires the cells to be non-selectable.

The Worksheet Protect dialogue box says:

Allow users of this worksheet to:

and the boxes that you tick such as 'Select Unlocked Cells' allow users to select unlocked cells. Surely if you do not tick the 'Select Locked Cells' then 'All users of this Worksheet' should not not be able to select 'Locked Cells' - if it was just so that you couldn't change the content, then I would expect the option to say, 'Select but not change Locked cells'.

Regards

Sandy
 

Enash Sregor

New Member
Joined
Nov 23, 2006
Messages
6
Hi Erik,

Just a quick one - I have password protected buttons to run certain macros to check the accuracy of inputted data.

I know that some (if not all the users) will try and use this button which is reserved for Tutors - if an incorrect password has been entered is there a way via the button macro, to force the whole application to close down (without any alerts or prompts) and without further notice or interaction from the user?

Regards

Sandy
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi, Sandy,

I really love to answer questions, but this one is off-topic. Because this thread is already "answered" there are really less people looking to help here. So in fact it's kinda asking the question only to me (in fact you do when using "Hi, Erik"). If you cannot find the answer on the Board, then you can start a new thread.

best regards,
have a nice weekend,
Erik
 

Forum statistics

Threads
1,136,263
Messages
5,674,710
Members
419,521
Latest member
Jasonnie

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top