CODEWITHSUNDEEP
excelvba
viviansplatoon
viviansplatoon

Reputation: 148

VBA protected cells but still allowing edits

I have a python script that edits contents of a certain column and VBA that checks to see if this column has changed. When it detects a change, it locks the column from user edits. It is successful in protecting the worksheet but the cells can still be edited. I can't understand why this is happening.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Protect As String
Dim NotProtectRange As String
ProtectRange = "A1:A1049576"
NotProtectRange = "B1:XFD1048576"

If Not Intersect(Target, Target.Worksheet.Range(ProtectRange)) Is Nothing Then
  Worksheets("Sheet1").Unprotect
  Range(NotProtectRange).Locked = False
  Worksheets("Sheet1").Protect UserInterfaceOnly:=True, Contents:=True

End If

End Sub

I expect that when I double click the cells in the range ProtectRange, I should not be able to edit them. But instead, I can edit them. So how do I fix this?

Upvotes: 0

Views: 656

Answers (1)

Tim Williams
Tim Williams

Reputation: 166196

If the cells are not locked then protecting the sheet does not prevent editing.

Me.Range(ProtectRange).Locked = True

should resolve the problem.

Upvotes: 1

Related Questions