VBA locked cell

pcquestions

New Member
Joined
Jul 23, 2010
Messages
2
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
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
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

Legend
Joined
Feb 18, 2002
Messages
13,690
You can also use a DoubleClick to edit your cells
Code:
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

New Member
Joined
Jul 23, 2010
Messages
2
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
 

Forum statistics

Threads
1,089,668
Messages
5,409,636
Members
403,273
Latest member
Yandos

This Week's Hot Topics

Top