[Excel VBA] How to Make Specific Cells Uneditable (Using Worksheet_Change)

2025年12月29日

In Excel, making cells uneditable usually requires the steps of “Locking Cells” followed by “Protecting the Sheet.”

However, setting this up every time can be tedious. I investigated whether a similar function could be implemented using VBA. As a result, I found a method using the Worksheet_Change event to instantly cancel inputs to specific cells.

What We Want to Achieve

Prohibit editing in cells J5 to J7 and M5.
Cancel the input and display an error message the moment a value is entered in those cells.

Implementation Code (Using Worksheet_Change)

Paste the following VBA code into the code window of the target worksheet (e.g., Sheet1).

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("M5,J5:J7")) Is Nothing Then
        Application.EnableEvents = False
        Application.Undo
        Application.EnableEvents = True
        MsgBox "Editing this cell is not allowed.", vbExclamation
    End If
End Sub

How This Code Works

Intersect(Target, Me.Range("M5,J5:J7")): This checks if the cell being edited is within the prohibited range.
Application.Undo: If it is in the prohibited range, this command cancels the edit immediately.
MsgBox: Finally, a popup error message is displayed to the user.

Important Points

Preventing Infinite Loops: You must use Application.EnableEvents = False. Without this, Application.Undo will trigger the Change event again, causing an infinite loop.
Complex Operations: Be careful when using this with merged cells or sheet protection.
Bulk Actions: If you select multiple cells to delete or paste data at once, this code might not behave as expected.

Use Cases

History Protection: Preventing re-editing of cells that have already been filled.
Templates: Creating internal company templates where only specific items should be editable.
Forms: creating checklists or application forms with strict input restrictions.

Summary

By combining the Worksheet_Change event and Application.Undo, you can instantly cancel edits to specific cells.

This method allows you to protect cells safely and manage your sheets flexibly without using the standard “Protect Sheet” feature. Please try using this to improve your work efficiency and template management.