Data not protected when selection changes

crazyoldcat

New Member
Joined
Aug 4, 2009
Messages
19
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.
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Do you have any other VBA code in that Sheet module or the "ThisWorkbook" module?
Or do you have any code that disables events?
 
Upvote 0
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
Code:
? 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.
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top