Forum Discussion
Lock cell without protecting worksheet
You could use VBA code to prevent some cells from being edited, but that only helps with accidental edits. Someone who knows a bit about Excel can easily circumvent it by either opening the workbook without allowing macros, or by temporarily disabling events.
Here is what you could do:
- Right-click the sheet tab.
- Select 'View Code' from the context menu.
- Copy the code listed below into the worksheet module.
- Change A2,C2,A4,C4 to the list of cells that you want to protect. You can use ranges too: A2:B10,D2:H10
- Switch back to Excel.
- Save the workbook as a macro-enabled workbook (*.xlsm).
- Instruct users to allow macros when they open the workbook.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("A2,C2,A4,C4"), Target) Is Nothing Then
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
End If
End Sub
Hi There, This works perfectly, I have a question, How do i make it work in a macro when i need to copy the same range of cells to a few lines below?
can you please help,
Thanks
email address removed for privacy reasons
This is a long discussion. Which code are you referring to?
Hi Hans,
we are talking about the locking of cell, or a range of cells ( for a formula as an example) without protecting the sheet,
Please advise how to deal with this,
Thanks HansVogelaar
In the code
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Range("A2,C2,A4,C4"), Target) Is Nothing Then Application.EnableEvents = False Application.Undo Application.EnableEvents = True End If End Subyou can change the range "A2,C2,A4,C4" to refer to the entire range or set of ranges that you want to "lock", for example:
If Not Intersect(Range("D2:F50, H2:K50"), Target) Is Nothing Then