vba to lock the entire row

Hi,

Below is the code which update the timestamp in the adjustment column when the value is entered in C or E column.

Please Login or Register  to view this content.

Now my last step is to lock the entire row when E column is updated or lock all filled cell. So next user update on the the next cell/row which is not updated. I tried lot of option, but my entire sheet is getting locked instead just a row.

Good afternoon shiva_reshs

What you will need to do is highlight all cells, then go to Format Cells > Protection and untick the Locked box to unlock all your cells.

Then set workbook protection through your Review tab : all cells should be editable as you will have unlocked them.


You can then use code like this to lock an entire row (note that the worksheet will need unprotecting first, and reprotecting after).

Please Login or Register  to view this content.

HTH

DominicB

Good After DominicB,

Thanks for assist. I tried your code but it turns red on below line with syntax error. am i missing something to tag?

Please Login or Register  to view this content.

Hi shiva_reshs

Apologies - I used without testing. Try this :

Please Login or Register  to view this content.

Needless to say, make sure it's being used after you define xRow.

HTH

DominicB

Hi Dominicb,

This works perfect. But as you see from the code my vba capture the timestamp when the cell value is changed. With the code that i have added to lock, the moment i touch the cell in E column, the entire row is locked. This doesnt allow me to update the E cell to update as the row is already locked.

Any workaround for this?

I tried to do this to overcome this challenge.

Please Login or Register  to view this content.

And did module call

Please Login or Register  to view this content.

now am getting 424 Object required on xRow = Target.Row

Couple of attempt. this is i tried to make it neat.

Please Login or Register  to view this content.

After first attempt which is successfully done, when i do it on second line i get below error msg on the red line above.

Run - time Error 1004

Unable to set the Number Format property of the Range Class

Hi shiva_reshs

Let's go back to post 5 first.

Can you post the whole file - I don't need all the data, just a few rows of dummy data.

Explain to me what happens and what should happen and we'll take it from there. Once we get that working, we'll have a look at any outstanding issues from what you have put in posts 6 and 7.

HTH

DominicB

here is the attachment. Am sorry, should have done that earlier.

Hi shiva_reshs

Have a look at the attachment.
I have removed your code and replaced it with a module that should put in a date into column F when column B is completed. I have also added a module that locks the row when an option in column E is selected.

Let me know how this goes.

HTH

DominicB

Hi Dominicb,

Thanks a lot. Made some modification per my need and now it works perfectly for my need.