VBA protection code not working, & worksheet protection is lost if a cell value is deleated.

  • Not sure, I think I should be able to but I can't at the moment. In the meantime, please can you add code tags to your original post? They are currently attached in the first bit of the post which doesn't need them.

  • I've added code tags and moved the question. There is nothing in that code to affect sheet protection. It seems to work fine on what it is supposed to do, i.e. prevent changes in the specified range.

  • royUK Thank you for moving the post. I found the problem, But I have no solution. this piece of code was causing the other lines to either not run or Partially run.

    If a solution could be found then, I would be a happy camper. Any idea why it was causing the problem & how to resolve it.

  • The unprotect code is not being used and the worksheet is not protected in the example.

    The code below isn't used by the code that I can see, UserInterFace Protection is best applied in the WorkBook_Open event

    Public Sub AllowMacros()
    'This allows a macro to run & keep the sheet protected.
    Me.Protect UserInterfaceOnly:=True
    End Sub

    Target can be one cell or a group of cells.

    Why are you using this?

     For Each c In Target

    If 1 = 1 Then - this will always be true

    I cannot see any thing in the code that affects the sheet's protection that is in use.

    Your code could do with a tidy up, variable declarations should be at the top of the Procedure so they are easy to follow.

  • I've moved the protect line so that it is not inside the IF statement. Check this amended code.

  • royUK I did test your code, & had a problem with a few of the lines. I REALLY appreciate the help you are giving me. Since I am working under a deadline, will comment out the code that changes the year & if I have time left & will come back to it. Again thank you for taking time out from your busy schedule.

  • I found out that the file was corrupted. That is why you make a back up, so that solved that problem. I mentioned a deadline, because I am retiring on 12December. I am trying to finish all my projects. Again thanks for all your help & from the group.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!