Locking Cells after Entry

[faisal.ta]

Hi
In an excel sheet after we do entry in a cell, this value should be copied to another sheet and at the same time that cell should be locked and the operator should not be able to overwrite that cell with with any other values.

[JBeaucaire]

In the sheet module:

Please Login or Register  to view this content.

[faisal.ta]

Hi
I tried the same, but its showing error
Complie Error:
Next without For.

Also are these codes common for office 2003 and 2007?

[JBeaucaire]

Code fixed above.

[faisal.ta]

Thanks. its locking the cells now.
But if we type in cell A1, then its getting copied in next sheet, but Cell A2 is also getting locked. What we need is Cell A1, after entering a value, we should not be able to edit in that cell where as Cell A2 and others must be open for editiong. Then after we enter a value in next cell, then it should get locked. and so on.

[JBeaucaire]

That's not what the macro does. It only copies the cell changed and locks the cell changed. I can't get the macro to do anything else than that.

[faisal.ta]

OK, In that case can we make a clause that say Cell A1 to Cell A20, if the values are entered then it performs this action, Thats copy them to next sheet and then Lock the sheet. Only After Cell A1 to A20 is thru then it should perform this action

[JBeaucaire]

This will perform the action only when one of the cells A1:A20 are changed.

Please Login or Register  to view this content.

[faisal.ta]

Thanks for the help. But what i meant was Cell A1 to A20 only after all these cells are entered with a value, then it should copy and lock the sheet.Like when we have some check clauses for factors in cell 1 to 20, then when all of them are checked, only then it should lock the sheet.

[JBeaucaire]

Lock the whole sheet? Lock those cells? Is the sheet already protected or does this protect it for the first time?

[faisal.ta]

It Should Lock the whole sheet.
No the sheet is Open for editing, but after the entry in Cell A1 to A20, it should Protect the whole sheet.
But All Cells A1 to A20 should be entered with a value, say Checked OK. So if all the cells A1 to A20 is checked OK then it should protect and lock the sheet. If any 1 of the cells between A1 to A20 then it should not protect and lock the sheet.

[JBeaucaire]

Try this:

Please Login or Register  to view this content.

[faisal.ta]

Thanks
That really worked and thanks a lot.
And one final question, the password for the same is blank now.
Can we get some password, and can that be hidden in the VBA

[JBeaucaire]

Just add a password to the protect command.

Please Login or Register  to view this content.

To hide the VBA from prying eyes, you'll have to right-click on the VBAProject in the VBAProject window and select VBAProject Properties...you can set a password on the Protection tab. The next time you open the workbook, you'll need the password to see the vba.