Lock the column on Specific Text Based

[AVG123]

Hello Friends..........

is it possible to lock the entire row (means Readonly) if i type Locked and if i type unlocked then we can make changes in the row......

for better understand i am uploading a sample file please see the file

Column-A to J have some data.............

1. i want when i type "Locked" in Col-K then the entire row should be Uneditable.......
2. And it should be editable when i type "Unlocked" thats it

see the sample file

[KOKOSEK]

1. i want when i type "Locked" in Col-K then the entire row should be Uneditable.......

2. And it should be editable when i type "Unlocked" thats it

How can you type anything in uneditable cell?

[AVG123]

Only Col K should remain editable

[zzzeddy]

Hi AVG123

For a very simple solution that doesn't require protection or locking, you could just make sure that the User cannot select a cell in any row that had "Locked" in column [E] of that row.

The code for this is:

Please Login or Register  to view this content.

..see attached workbook to see this working

zeddy

[mehmetcik]

Right Click On Your Sheet Name At the Bottom Of Excel and Select View Code
Paste this code in the module that opens and close it.

Please Login or Register  to view this content.

[zzzeddy]

Hi AVG123

..note I used your sample file which had column [E] for the Locked/Unlocked setting.
You can see that this is easy to change to any other column as you require e.g. column [K] etc etc etc

zeddy

[AVG123]

Sir it is not working

[zzzeddy]

Hi

I like the brevity of mehmetcik's code, but I would use..

Please Login or Register  to view this content.

zeddy

[zzzeddy]

Hi AVG123

..when you load my posted sample file, you need to have macros enabled.

zeddy

[zzzeddy]

Hi AVG123

..and then just move the cellpointer around to see what happens.

zeddy

[AVG123]

Nothing is working something missing

[KOKOSEK]

Please Login or Register  to view this content.

[AVG123]

When i typed from Locked to Unlocked........after that it is again uneditable

[zzzeddy]

Hi AVG123

Are you having problems with my posted file???
I have tested this before posting.
So, in case you have turned event-tracking OFF I suggest you start again by coming out of Excel completely, then starting a new Excel session, then loading my posted file with macros enabled, then testing it by moving the cellpointer about.

zeddy

[AVG123]

Giving error 1004 "Select method of Range class failed"

[zzzeddy]

Hi AVG123

..with my posted file, you can change the setting from Locked to Unlocked on any row.
When a row setting in column [E] is set to "Locked", you cannot move the cellpointer to any cell on that row (other than column E) and so you cannot edit any cell to change it.
If you change the setting in column E to anything other than "Locked", it will immediately allow you to select/edit any cell in that row.

zeddy

[zzzeddy]

Hi AVG123

I downloaded the file I posted and tested it OK in Excel2010 and tested OK in Excel2016

..something else is interfering at your end.
..you may have some other add-ins loaded or you are not using the file I posted.

zeddy

[KOKOSEK]

When i typed from Locked to Unlocked........after that it is again uneditable

Before you run it please be sure than col E is unlocked/unhidden in properties then works 100%.

[AVG123]

Thanks sir it is working ............if you don't mind can you alter your program
i want when anyone change from Locked to Unlocked or Unlocked to Locked always prompt a Password to change it.........