Lock certain cells from edit- test if select cell name starting wi

[Daniel W]

Have an excel template where I want to lock some of the fields from user edit.
Manage to write (cut and paste) some code that almost fulfill my demands.
When the user tries to change or delete one cell that is "locked" a popup is
displayed and then the value is set back to the original.
But there some issues I would like to get help with.

1) Instead of counting out the cells that should be locked, as in the code,
I want to use that all those cells have a defined name starting with Z.
If the cell name starting with Z it should not be editable.
How can I do this?
How can I get the name(s) of the selected cell (or cells)?

2) If I fill in any value in a locked cell and press enter or set focus to
another cell the code works as intended. But if I set focus to a textbox
instead of a cell it will not work, the cell is changed to new value.
Do any have an idea how this can be solved?

Code
Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewValue As Variant, OldValue As Variant
Dim Test1(1) As Variant
Dim i As Integer
Dim Cell_locked As Boolean
Cell_locked = False
Test1(0) = "B3"
Test1(1) = "C3"
For i = 0 To 1
If Not Intersect(Target, Range(Test1(i))) Is Nothing Then
Cell_locked = True
End If
Next i
If Cell_locked = True Then
NewValue = Target.Value
Application.EnableEvents = False
Application.Undo
OldValue = Target.Value
MsgBox "You cannot change the contents of this cell.", 16, "Locked
cell"
Target.Value = OldValue
Application.EnableEvents = True
End If
End Sub

Thankful for any help and improvement of the coed.
Daniel

 

[Patrick Molloy]

how will you recover the original value since the change event fires AFTER
the value changes? I'd suggest a hidden cooy of the same sheet
Also, why don't you use worksheet protect , then allow certain cells to be
'unlocked ' so that the user can edit them. by default, when a sheet is
protected, so are all its cells - you need to adjust the parameters of the
protect method

Range("A1").Name.Name returns a defined name if there is one, or errors
Names contains the named ranges

Sub checker()
Dim nm As Name

For Each nm In ThisWorkbook.Names
Debug.Print nm.Name, nm.RefersToRange.Parent.CodeName
Next


End Sub

 

[Daniel W]

Hi Patrick and thanks for your suggestions
the original value is recovered, thanks to the use of undo I guess so that
part works pretty OK.
Have tried to protect the cell but had some problem with that: in my case a
locked cell is linked to a custom property which is updated by a PDM system
and if I procted the sheet the custom properties could not be updated.
However I found some code here that work as a solution for my question 1
The code below should then replace the section For i=1 ... Next i

Dim NM As Name
For Each NM In ActiveWorkbook.Names
If Intersect(Range(NM), Target) Is Nothing Then
Else
If StrComp(Left(NM.Name, Len("Z")), "Z", vbTextCompare) = 0 Then
Cell_locked = True
End If
End If
Next