Lock and protect selected excel rows with VBA code

[SmallJack]

Hi Gurus,

I've an excel worksheet with sama data to be protected and some data to be updated by our users on a regular basis.

My query is, will it be possible to use VBA code to password protect any selected row after updating? Meaning, once the user has finished updating that particular row, maybe with a click of a button, that row will be procteted and no additional change allowed. Only admin who has the password will be able to make any additional changes.

However, the rest of the row will still be accessible and updated by user till it's protected.

Appreciate for your help.

Thanks.


- Jack

[NeedForExcel]

Yes!

That can be done.. Can you attach a sample workbook?

[SmallJack]

Hi Gurus,

I've attached a sample data in this post.

Users need to update rows in B2 to D11, yellow highlighted.

Once update is done, the row will be protected as explained previously.

Thanks for your help.


- Jack

[NeedForExcel]

So will the user fill in 1 row at a time and then that particular row is to be locked, or will the user fill in all the rows from 2 to 11, and then all the rows should be locked at once?

[SmallJack]

Hi Gurus,

User will need to update 1 row at a time, once done, that particular row will be locked.

Thank you.


- Jack

[NeedForExcel]

Hi,

Please see the file attached.. The password to unlock the sheet is asd

Let me know if it is as required.

Do not forget to enable Macros..

[HaHoBe]

Hi, SmallJack,

Right-click on the worksheet-Tab, choose View Code and enter the following code into the code window:

Please Login or Register  to view this content.

Code will be triggered f any column form E on is entered or changed and will lock Columns B to E.

Ciao,
Holger

[SmallJack]

Hi Holger,

Thanks for your help.

I've tried, but it locked all the rows, including those raws which have not been updated.

I've attached my test data, I'm unable to update row 7 onwards.

Please advice. Thanks.


- Jack

[NeedForExcel]

Hi Holger,

Thanks for your help.

I've tried, but it locked all the rows, including those raws which have not been updated.

I've attached my test data, I'm unable to update row 7 onwards.

Please advice. Thanks.


- Jack

Hi,

Did you happen to check out my solution?

[SmallJack]

Hi Gurus,

Thanks for the reply.

I've tried your solution, but there are some issues.

Users are only allowed to update the yellow highlighted area only.

I've tried to lock outside yellow area with protection, but when I click on the button, it removes them.

Also, I hit with an error when trying to update raw 7, see attached (SampleData-Test2.xls).

The code should be able to cater the ongoing update within the yellow area.

Appreciate for your help.

Thank you.


- Jack

[HaHoBe]

Hi, Jack,

jusrt unprotect the sheet again, run test and then use the altered Code behind the Sheet:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Besides: you should be more precise on what you really want. Iīm out of this thread.

Ciao,
Holger

[NeedForExcel]

Check it out now

[HaHoBe]

Please Login or Register  to view this content.

@NeedForExcel:
Please enter Test into Cell B11 alone and run your code in your attachment. I doubt that all empty rows between 3 and 10 should be locked as well.

Ciao,
Holger

[NeedForExcel]

Please Login or Register  to view this content.

@NeedForExcel:
Please enter Test into Cell B11 alone and run your code in your attachment. I doubt that all empty rows between 3 and 10 should be locked as well.

Ciao,
Holger

Hi Holger,

I have kept that in mind, however keeping in mind, normally data entry will happen one row after another, that's why like that..

[HaHoBe]

Hi, NeedForExcel,

no need for a full quote here - maybe have a look into the Forum Rules (#12).

Any entry into Column B would have to leave Column C and D open for more entries in that row or how will you fill the entries in these columns when the row is locked?

Ciao,
Holger

[NeedForExcel]

Any entry into Column B would have to leave Column C and D open for more entries in that row or how will you fill the entries in these columns when the row is locked?

Agreed! But the user will lock the Row only when he finishes filling all the 4 Columns on that particular row wont he? Atlease that's what I think.

Best if the OP states if he requires something otherwise.

[HaHoBe]

Hi, NeedForExcel,

the yellow highlighted area holds Columns B to D (which is 3 Columns) and the rows 2 to 11. With your code (which could be started at any time even if not entry or change was made) the whole area between B2 and F and the last entry row in Column B is blocked. I canīt agree on that from what I read that from post #10.

Ciao,
Holger

[SmallJack]

Hi All,

Thank you for your help and replies.

Greatly appreciate it.



- Jack

[SmallJack]

Hi Gurus,

How to incorporate solution given by Holger (post #13) into using a button to trigger the password lock?

Thought it'll be more user friendly for users to manually lock the entry only when they've really finished entering the data.

Thank you.



- Jack

[HaHoBe]

Hi, Jack,

Thought it'll be more user friendly for users to manually lock the entry only when they've really finished entering the data.

Sorry but to rely on extra action isnīt what I would call user-friendly.

Please Login or Register  to view this content.

Ciao,
Holger

[SmallJack]

Hi Holger,

Great, works perfectly.

Thanks for your help and replies.

Have a nice day.

[HaHoBe]

Hi, Jack,

maybe move the command to unprotect the sheet before the loop instead of using it inside there (itīs needed just once).

Thanks for the fedback and the rep.

Ciao,
Holger

[SmallJack]

Hi Holger,

Thanks for the update.

With the given code, but not using a button trigger method, can I check if it’s possible to protect multiple worksheets on opening or closing the excel file? For example of multiple worksheets: Sheet1, Sheet2, Sheet3 and soon.

Appreciate for your help.

Thank you.

[SmallJack]

Hi Gurus,

Anybody else can help out? Thanks.

[SmallJack]

Hi All,

Never mind, closing the thread now.

Thanks everyone