Unlock a cell based on checkbox value in another cell

[EJohnson79]

Hello.

I have a protected sheet (no password) that I would like cell L7 to unlock if the checkbox in J7 is checked or remain locked if the box is not checked. I would like this to continue down the sheet; L8 unlocked if J8 is checked etc.

Any help is much appreciated!!

[Richard Buttrey]

Hi,

You've complicated the process by using Check boxes. You could just as easily have had the user click in column J which could have triggered the relevant part of the following macro. This macro first has to identify where the checkbox is located and then lock/unlock the relevant cell. And of course you'll need to point all the checkboxes code to this Procedure.

Please Login or Register  to view this content.

[EJohnson79]

Thank you Richard.

I tried pasting this code but the cell in column L did not unlock for editing when the checkbox was checked. If the checkboxes are complicating what I'm trying to do, would a yes/no dropdown in column J simplify it or no?

-E

[Richard Buttrey]

Hi,

Yes it's much simpler just to detect a cell change, or in this case a double click.

I've removed the checkboxes from J6:J7 and removed the data validation to show you an alternative.
Double clicking these cells will toggle a Y/N and Lock/Unlock the column L cell on the same row.

[EJohnson79]

Thanks again Richard. I agree the Y/N toggle will be simpler; thanks for the suggestion. However; when I opened the revised file, I'm not able to toggle the Y/N by double clicking. If I change the Y to N in J7, L7 does not unlock for editing. Perhaps I'm missing something?

[Richard Buttrey]

Hi,

Toggling to N locks the L cell. It sounds like you want this the other way round. In which case just change the line in the
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
procedure

Target.Cells(1, 3).Locked = True
to
Target.Cells(1, 3).Locked = False

and the line
Target.Cells(1, 3).Locked = False
to
Target.Cells(1, 3).Locked = True