Enter Value in Cell - Unlocks Entire Row

Hi,

Can I enter a value into column A (any row) that would unlock the entire row whilst leaving the rest of the sheet protected?

Example - in A51 I would enter "Z" and that would unlock the row.

Thanks
Regards
Chris

Yes can be done with VBA. Not by formula. Want to go in that direction?

here's a start

Please Login or Register  to view this content.

Make all A1:A100 UNLOCKED (format cells protection)
Make B1:B100 LOCKED (format cells protection)

Put this code the the Sheet's code (right click TAB and view code)

Note that this will UNLOCK any row when you type z in that column. It does not change back (yet)

Hope you can manage, otherwise ... we'll be here.

Originally Posted by rwgrietveld

Yes can be done with VBA. Not by formula. Want to go in that direction?

Heh...that's probably why this Q is in the programming forum.

Zimbo,

1) Right-click the sheet tab and select VIEW CODE
2) Paste in these sheet-event macros.

The first will protect the sheet again when it is activated (brought up onscreen) but also set the flag that allows macros to change the protected worksheet even though it's protected. Edit the password.

The second is the macro that unlocks a row if "Z" is put in column A anywhere.

Please Login or Register  to view this content.

3) Close the editor
4) Save the workbook as a macro-enabled workbook
5) Switch to a different sheet, then come back...this triggers the first macro silently
6) You can now enter a Z in column A and the rest of the row will unlock.

This requires that you previously unlocked column A before the sheet was protected.

Go for JB's solution. It's more elegent as it includes
1) UserInterfaceOnly
2) It extends over the full range of column A and
3) If you Ctrl+Enter "Z" values in a few cells (in column A), JB's function will do them all.

Like so:

Please Login or Register  to view this content.

If you want to continue tweaking what is allowed and what is not on your protected sheet, turn on the macro recorder and let it record you protecting a sheet and setting all the flags the way you want them. Then look at that code to spot the items you need to add/subtract from the code.

I assume this means that column Q has to be left unlocked when the row locks again, yes?

Please Login or Register  to view this content.

When you decide you need another expansion to this macro, take a look at the macro you have been given so far and try to adjust it yourself. Post up your attempt so I can help you work through it.

If you understand what the code is doing, you can learn and maintain it on your own, yes?

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated. It is found across from the "time" in each of our posts.)