VBA locked cell

[pcquestions]

I have an spreadsheet that will be used by mutiple users I have created the following VBA code to lock the cells as data is entered, for that matter works p[erfectly, but when I need to change somenthing in the spreadsheet I have to unprotect every cell and most of the time I need to change 3 or 4 cells
There is a way to unprotect the sheet and make the changes and protect again?

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Unprotect "Password"
Target.Locked = True
ActiveSheet.Protect "Password"
End Sub

THANKS

 

[lenze]

Hi and welcome to the board!!!
Can't you just unprotect the sheet, make your edits and then reprotect?? You could write code to do this??

lenze

 

[lenze]

You can also use a DoubleClick to edit your cells

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Locked = True Then
rspn = InputBox("Password required to edit this cell")
  If rspn = "Password" Then
     ActiveSheet.Unprotect "Password"
     Application.EnableEvents = False
     myVal = InputBox("Enter your change")
     Target = myVal
     Application.EnableEvents = True
     ActiveSheet.Protect "Password"
     Exit Sub
  Else: MsgBox "You may not edit this cell!"
  End If
End If
End Sub

There is, however, a small glitch in the code which I can't seem to figure out. It makes the change, but still dispays the "Protection warning"

lenze

 

[pcquestions]

Hi Thanks for your answer but either I am doing something wrong or your the code is not working for me.

I can't just protect or unprotect the sheet because there are many users accessing this spreadsheet and I need them to enter new data, each workbook has around 30 sheets I need a code that locks the cell as the information is entered but also unlock, under a password, for me to make the changes at once, my code seems to be working but I have to unprotect every cell to make the change and also this workbook it will be reused next year, for that reason I will have to delete all 30 codes in order to have the blank cells, that were used before, open to be used again and save as blank and past all the code over again to every sheet.

Could you help me on this?

Thank you