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

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,063
Messages
5,622,470
Members
415,897
Latest member
Barry18180

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
Top