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]

Try this:

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
    
    [COLOR=#ff0000]ActiveSheet[/COLOR].Unprotect Password:="password"
    xRg.Locked = True
    [COLOR=#ff0000]ActiveSheet[/COLOR].Protect Password:="password"
    
End Sub

And note that you need to make sure that your cells are all unlocked initially. By default, Excel cells are all locked and the sheet is unprotected.
So you need to make sure that you have change them to be unlocked for your code to work properly.

 

[Fennek]

Hi,

try

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Parent.Unprotect
Target.Locked = true
Target.Parent.Protect

Don't set a password, it is meaningless.

regards

 

[Joe4]

Don't set a password, it is meaningless.

Not if you are using a strong password (sometimes people just simplify it here for posting purposes, as they do not really want to post the real password).
Otherwise, users can easily unprotect it and bypass the measures you are trying to enforce.

 

[crazyoldcat]

Try this:

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
    
    [COLOR=#ff0000]ActiveSheet[/COLOR].Unprotect Password:="password"
    xRg.Locked = True
    [COLOR=#ff0000]ActiveSheet[/COLOR].Protect Password:="password"
    
End Sub

And note that you need to make sure that your cells are all unlocked initially. By default, Excel cells are all locked and the sheet is unprotected.
So you need to make sure that you have change them to be unlocked for your code to work properly.

I have tried both methods listed, thank you by the way, and IO am still not getting the result I am looking for. Is it possible to send someone the spreadsheet to see if they can figure out what I am doing wrong?

 

[Scott T]

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

 

[Joe4]

I cannot download files from my current location (security policy).

Have you manually gone in and unlocked all cells to start?
Walk us through an example which is not working for you and tell us exactly what is happening.

 

[crazyoldcat]

I have unlocked all cells to start by selecting all and then "format cells" I then click on the protection tab and uncheck "locked". I click on "OK" and then save the changes. When I add the VBA script I save it as a macro enabled file then protect the sheet. At this point everything seems to be going as designed. I enter data in a cell and tab or enter to change cells. the sheet changes to unprotect and then back to protect right away. The part that is not working is that the cells are not locking/protected once there is data in the cells. I can go back in and write over the data and even delete the data. When the data in those cells is deleted THEN the cells are locked and I have to unprotect the sheet to unlock them.

Thanks in advance for your help.

 

[Joe4]

It seems to work just fine for me.

What cell, exactly are you entering your data in?
Note that you have written your code so that it only applies to the range A5:W228.
So anything entered outside of that range will not be affected.

 

[Scott T]

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.