Protecting new cells in allready protected worksheet?

[Sprucemoose]

I'm currently working on a medical research project where large amounts of data is about to be registered in several worksheets. Now, I won't be registering the data myself (I got someone to do that for me), but I want to reduce the risk of input error.

So far, I have locked the first column and row (Fixed patient ID on first column and variable names on first row), and I have set up data validation ranges for all input cells. When I protect the sheet (password protected), everything seems ok. I can then only input values specified through data validation and input is forbidden in the first row/column.

But...when all the values of a row (patient) is put in, is there any way of locking that row without going through the unprotect-lock-protect process?

I would like my workers to be able to input data in a protected sheet. But as soon as the data of a row is verified I need them to be able to add those cells to the protected range. I don't want them to be able to unprotect anything in the worksheet, but I want them to be able to protect the values of certain cells. Is this doable? Any help i very much appreciated.

[Domski]

Have a look at the attached.

It uses a VBA worksheet change event to detect when a change has been made to column E and then locks the whole row. Right click on the sheet tab and select View Code to see the code.

Dom

[Sprucemoose]

Thanks for replying. Initially I had hoped to avoid any programming, but I realized that I couldn't if I wanted to achieve what I wanted. With a little help from a friend, some google, copy/paste, trying/failing etc. I ended up with this macro:

Please Login or Register  to view this content.

I really don't understand how everything in this macro works, but it does what I want . I put a "registered"-column at the end of the datasheet which only accept a value of "1" (or blank). When all data from a single patient is put in, putting a "1" in that last column and running the macro (assigned to Ctrl+R) marks the whole row with a certain background color and locks all cells in that specific row. To be able to modify the locked values after the macro is run, you need to unprotect the sheet manually.

[Sprucemoose]

Ok, I did some changes to the macro to add some functionality for the people who will be registrering the data. The code in my last post, the entire row was locked down, essentially making it a one-way route. If an error is noticed after the row is locked, they will have no way of correcting it without calling me for help. To accommodate such a scenario while still retaining the security of locking down rows, I made some changes:

Please Login or Register  to view this content.

Now, they can open up the rows again if they remove the "1" in the Registering column. They will still be locking down rows after each patient is registered, but they now have the ability to undo this to correct data if needed.

The whole purpose of creating this macro is to keep slippery keyboard fingers from messing up the data by mistake, and I think this will do it's job well.

I guess the programming could have been cleaner, but since this is my first try ever on a macro, it's the best I could do. For learning purposes, I would gladly accept tips for optimizing the code, but I think I'll leave it alone for now (If it works, don't change it and so on)...