Data not protected when selection changes

[crazyoldcat]

Hello,

I am working on a spreadsheet with VBA and am trying to make sure the cells are locked AFTER data is entered. What is happening now is that I can go in and edit the data as much as I want to but the moment I delete or clear the data the cell locks. This is quite the opposite of what I wanted to accomplish. I want the cells to stay unlocked until there is data in the cells then I want the cells with data in them locked and the data protected.
This is the script I am using.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim xRg As Range
On Error Resume Next
Set xRg = Intersect(Range("A5:w228"), Target)
If xRg Is Nothing Then Exit Sub
Target.Worksheet.Unprotect Password:="password"
xRg.Locked = True
Target.Worksheet.Protect Password:="password"
End Sub

Thank you in advance for your help.

 

[Joe4]

Ah, the nightmare of merged cells strikes again!
Avoid using them at all costs!!! They wreak havoc like this to your VBA code and other Excel tasks (such as sorting).

You can usually get the same visual effect as using merged cells without all the headaches if you use the Center Across Selection formatting option instead.
See: https://www.atlaspm.com/toms-tutori...er-across-selection-instead-of-merging-cells/

 

[crazyoldcat]

Ah, the nightmare of merged cells strikes again!
Avoid using them at all costs!!! They wreak havoc like this to your VBA code and other Excel tasks (such as sorting).

You can usually get the same visual effect as using merged cells without all the headaches if you use the Center Across Selection formatting option instead.
See: https://www.atlaspm.com/toms-tutori...er-across-selection-instead-of-merging-cells/

Thank you so much. After my light bulb moment about the merged cells I recreated the spreadsheet. It is working beautifully!!

 

[Joe4]

Glad to hear the mystery is solved!

 

[Scott T]

As previously stated merged cells should be avoided but I was wondering if the code could be make to work with merged cells and this seems to work


Change the line to

 xRg.MergeArea.Locked = True

But really merged cells should be avoided.