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.

 

[crazyoldcat]

I am picking random cells in the range and have experienced the same thing.

 

[crazyoldcat]

Wow. Maybe I am just not that smart. I am at a loss as to what I am doing incorrectly here with the document.

 

[crazyoldcat]

The original code you posted is working for me. If I type something in A5 then try to over wright or delete I get the protected cell message.

I guess I am not as smart as I thought I was. It will simply not work for me like that.

 

[Joe4]

Do you have any other VBA code in that Sheet module or the "ThisWorkbook" module?
Or do you have any code that disables events?

 

[crazyoldcat]

You can put the file on dropbox or similar service and post a link here.

this is the link for the file: https://www.dropbox.com/s/zp3m77tcty5pg9b/Acquisition and Disposition_TEST.xlsx?dl=0

 

[crazyoldcat]

Do you have any other VBA code in that Sheet module or the "ThisWorkbook" module?
Or do you have any code that disables events?

I have nothing else, not even formulas.

 

[Joe4]

this is the link for the file: https://www.dropbox.com/s/zp3m77tcty...TEST.xlsx?dl=0

I am unable to download files from those sites at my current location (security policy actually blocks those sites).
So I will leave it to Scott to take a look at.

 

[crazyoldcat]

You can put the file on dropbox or similar service and post a link here.

Link posted. I hope that helps. I never mentioned but it may be relevant. I have merged cells throughout the spreadsheet.

 

[Joe4]

I posted a link but I cannot find it now.

It appears to be up in post 15: https://www.mrexcel.com/forum/excel...ted-when-selection-changes-2.html#post4968848 (assuming the link you posted is correct).

 

[Scott T]

The merged cells are the problem. Only when you delete something does the code lock the cell. Without merged cells the code is fine.

If you put a stop in the code and then look at the range

? xRg.address

When typing something into A13 returns $A$13
But when deleting it returns $A$13:$C$14, the merged cells

Not sure if the code can be written to work with merged cells, I avoid them since they cause problems like this.