disable editing in cells without protecting worksheet vba

Question

I want to disable cell editing (direct typing into cell) but want to update that cell through code without protecting worksheet

Does anyone have any idea?

Answer 1

In the worksheet's code module:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("A1:A10"), Target) Is Nothing Then
        Application.EnableEvents = False
        Target.ClearContents '// Assuming you want to keep it blank
        Application.EnableEvents = True
    End If
End Sub

Then in your code whenever you want to change a value, just disable events before hand:

'// Will be deleted
Range("A5").Value = "TEST"

'// Will not be deleted
Application.EnableEvents = False
Range("A5").Value = "TEST AGAIN"
Application.EnableEvents = True

Answer 2

I had a similar issue and found a workaround. For whatever reason I couldn't get my macros to run correctly with the sheets protected so what I did is as a code to Pop up a message box for any cells that I didn't want the user to change. Then I added "Application.DisplayAlerts = False" to the beginning of any codes that needed to modify those cells and reset the alerts back to True at the end of those codes.