I am looking for a way to lock specific cells (namely X7, Y7, and range AD6:AD12) on attached file. The workbook and all worksheets are already locked, but I'm looking to protect some manually entered data. There are 65 sheets in the full workbook and if possible I'd like to only protect the current sheet once the data has been entered, not all of the sheets within the workbook.
Any help is appreciated.
Thanks
You'll need to put this code on every sheet. Normally, using select is a bad idea in VB, but so is merged cells. I had to select the target to select all the merged cells, and then work on the selection.
Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("Y7,Z7,AD6:AD12")) Is Nothing Then Exit Sub ActiveSheet.Unprotect Password:="LTW" Target.Select Selection.Locked = True ActiveSheet.Protect Password:="LTW" End Sub
One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.
Thank you, this is definitely a start. Is it possible to tweak this a little bit and make it assignable code? Rather than it always checking for an entry, could it be written so that it could be assigned to a button? When the operator enters the necessary data they could click the button which would then lock those cells.
If you want to do this manually, then do not put code on the sheets. Instead put this code in a regular module
This code works on whatever sheet is active. You can either put a button on each sheet (yuck) or you can map the macro to a CTRL key such as CTRL-L.Sub LockIt() ActiveSheet.Unprotect Password:="LTW" Range("Y7:Z9,AD6:AE12").Locked = True ActiveSheet.Protect Password:="LTW" End Sub
Thank you for both options. They both work well and I can use either. I see what you mean by the bad idea for merged cells in this instance (I had to tweak the range to read X7:Z12,AD6:AE12 in order to lock the required cells because X7-X12 are merged without getting an error message). I can definitely hit the ground running now with them.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks