Locked cell behavior

[JMoreno]

Is there any way to completely lock out a cell from a user deleting the formula/information contained in it? Even with the cells being locked and hidden on a protected sheet, all a user has to do is click on an unlocked cell and drag their selection into the locked cells. If they hit delete, all your supposedly protected cells have their formulas deleted!!! And a nice consequence of this, is that since the cell is locked, you can't easily go back to re-enter the formula that was deleted, because it will kick you out of the cell since we don't want to change anything, right? Fun stuff. Good program.

I have not found any solution through vba (SelectionChange Events) or anything on the web that can prevent this from happening. So what is the point of locking cells and protecting the sheet when it's so easy get around it?

The code below works great in the worksheet code section for protecting a locked and hidden cell individually. If the user specifically clicks on a locked cell, it kicks them out to the next adjacent unlocked cell. But if they knowingly or accidentally click and drag a selection into the locked cell, all bets are off, they can change whatever they want.
------------------------------------------------------------------------------------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target1 As Range)

If Target1.Locked = True Then
Worksheets(1).Protect
ElseIf Target1.Locked = False Then
Worksheets(1).Unprotect
End If

End Sub
-------------------------------------------------------------------------------------------------------------

Is there some kind of "RangeChange" event that can be set in the worksheet code? If not, there certainly should be one, so that if any cell within a range selection is locked, even if the selection started on an unlocked cell, it prevents the user from issuing commands like "delete everything and break the worksheet please"....
Even in version 14, Excel still feels like a Beta program....

 

[steve the fish]

Hi. Could you tell us exactly the process you go through to avoid the cell protection because I cant replicate it on 2010

 

[jtakw]

Hi,

I think the OP's problem is the Selection Change code he/she is using.
It unprotects the sheet as soon as user clicks on an unlocked cell, thereby leaving the locked cells unprotected and proned to the problem as described.
I don't understand the purpose of that code, OP should just have the sheet protected, period! And stop using that code. End of problem.

 

[JMoreno]

Hi,

I think the OP's problem is the Selection Change code he/she is using.
It unprotects the sheet as soon as user clicks on an unlocked cell, thereby leaving the locked cells unprotected and proned to the problem as described.
I don't understand the purpose of that code, OP should just have the sheet protected, period! And stop using that code. End of problem.

The problem is the classic one: Once the sheet is protected, YOU CAN'T FILTER! Also, protected sheets can cause unexpected problems with macros that need to modify a locked cell. Basically, the macro breaks.

I've found a clunky solution, but it seems to be about the only way to get these to work simultaneously. The code above was really designed to track click events, to try to keep the sheet in an unprotected state for most of the time, giving the user data entry, macro running, and filtering options, but protecting locked cells when a user clicks on them...hence, the purpose.

The click and drag part was an unfortunate side effect to this, so I've modified the code below:
----------------------------------------------------------------------------------------------------------------------------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target1 As Range)

Set WatchRange1 = Range("A1")

Set IntersectRange1 = Intersect(Target1, WatchRange1)

If Target1.Locked = True Then
Worksheets(1).Protect
End If

If IntersectRange1 Is Nothing Then
Worksheets(1).Protect
Else
Worksheets(1).Unprotect
End If

End Sub
------------------------------------------------------------------------------------------------------------------------------------------------------

This requires setting up a "psuedo-button" in cell A1, which I've labeled "Unlock Filters". Of course, since the sheet will open in a locked state at this point, any macros will need a "Worksheets(1).Unprotect" line before any code that tries to modify locked cells. Also, it's good to set the Filters to "on" in the Autpen vba code as follows:
--------------------------------------------------------------------------------------------------------------------------------------------------------
If Not ActiveSheet.AutoFilterMode Then
ActiveSheet.Range("A2:K2").AutoFilter
End If
--------------------------------------------------------------------------------------------------------------------------------------------------------
The result of all this:
1) Users can enter and modify any data in unlocked cells while:
2) Also being able to use their macros AND being able to filter by clicking "Unlock Filters"
3) Any subsequent click (including tab) on any other cell than A1 turns the sheet protection back on, thus protecting locked cells from single or range selection

 

[jtakw]

Hi,

If you protect the worksheet via VBA in the WorkBook open event and set UserInerfaceOnly = True, your macros should run without further intervention.
I'm posting via my phone and am unable to provide a sample, sorry.

 

[jtakw]

Hi,

Also set EnableAutoFilter = True

 

[JMoreno]

Finally I found a one-shot piece of VBA that seems to do all the things I was hoping, without the mess. Before, it seemed like nothing I did would allow the Auto-filter to work when protecting a sheet. Then when I finally figured out something that worked, the column groups didn't want to open or close (the protected sheet check box list doesn't not make it clear how to fix that). Overall, setting protected sheets seems to be a nightmare of unexpected consequences, and seems to be made difficult on purpose. No sane person would write programs like this except for job security reasons!

Anyway, this code appears to protect the sheet, block any type of click in the locked cells, allow filtering, AND allow groups to be opened and closed (of course, drop this in Autpen):
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ActiveSheet.protect DrawingObjects:=True, Contents:=True, AllowInsertingColumns:=True, AllowDeletingColumns:=True, _
AllowFiltering:=True

With Worksheets(1)
.EnableOutlining = True
End With
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

[JMoreno]

Ack, that was wrong, but so close. It's actually this:
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
With Worksheets(1)
.protect UserInterfaceOnly:=True, DrawingObjects:=True, Contents:=True, AllowInsertingColumns:=True, AllowDeletingColumns:=True, _
AllowFiltering:=True
.EnableOutlining = True
End With
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

[jtakw]

Stop whining, you've already contradicted yourself more than once on how Excel & VBA works.
Learn to use it properly, learn how to use VBA efficiently, and maybe even how to write your own codes, then maybe you'll realize how versatile Excel is.
Otherwise, find another program or build your own for your needs.