lock/unlock rows based on values in cell

**aravindhan_31** · 10-22-2010, 11:29 AM

Hi,

I would really appreciate if you could help me on this...

I will enter all the details in columns A to E
I need a macro to protect all the rows of Column B,C & E( from row 2) if values in column A is "OK".
If anything has to be changed in any of the rows in Column B,C & E, the values in column A has to be changed to "Amendment" only then that particular rows should be unlocked

If again the value is changed to OK in col A, then lock the cells back.

that is

data in columns B,C & E are always to be protected if values in column A is "OK" & should be unlocked if values "Amendment"

I have attached the sample file for the reference.

Thanks
Arvind

**Paul** · 10-22-2010, 12:26 PM

Hi Arvind,

The following code would go in the worksheet module (for the sheet that contains your data). It will be triggered whenever a change is made to a cell in column A. Be sure to first select columns A and D and open the Format Cells dialog -> Protection tab -> uncheck 'Locked' so that you can edit those cells when the worksheet is protected. Then protect your worksheet. If you use a password to protect it, add that to the Unprotect and Protect lines in the code, e.g.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

**aravindhan_31** · 10-23-2010, 12:30 AM

thanks..it works.... i will manipulate the code a bit and try..

**aravindhan_31** · 10-25-2010, 08:03 AM

Hi Thanks for your help,

instead of making only column B,C & E shall we make all the columns except A for the input
and also I am facing an issue while running macro recorded for hiding some columns.

so how the macro should run is?

I have recorded a macro called ( Seller View) which hides some columns in my sheet and makes visible only some columns ( I have attached the file with macro)

So If I apply the condition as above, if any cells in column A has "OK" protect all these VISIBLE columns and if it is "Amend" then allow the user to edit.

now i have another macro called "Norm View" which hides some columns in my sheet and makes visible only some columns
So If I apply the condition as above, if any cells in column A has "OK" protect all these VISIBLE columns and if it is "Amend" then allow the user to edit.
I have about 15 different views recorded in a macro, and 1 macro to unhide all the columns.

I am getting error if use these macros and run your macro provided above

can you please help me on this?

thanks and regards
Arvind

**Noor Hussain** · 06-14-2012, 07:36 AM

I tried in attached sheet the above code but it didn;t work

Can u plz guide me i attached the work sheet what i make mistake

Regards
Noor

**Paul** · 06-14-2012, 03:33 PM

Hi Noor, unfortunately...

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.