Unlock password protected cell based on another cell

[gui]

Hi all,

I'm currently creating a simple Excel form which will be circulated to about 100 people to be filled out. I have password protected it (for the sake of the argument, password is 1). Cell C40 contains a Form Control (not ActiveX) Option Box with just Yes or No. They are linked to cell L40, which returns the values 1 for 'Yes' and 2 for 'No'. What I need to do is, when someone chooses 'Yes', therefore cell L40 = 1, for cell D40 to be unlocked, for a person to enter the reason for the Yes. I've created the following code but it's not working:

Private Sub Worksheet_Change(ByVal Target As Range)
   
    If Range("L40") = "1" Then
        ActiveSheet.Unprotect Password:="1"
        Range("D40").Locked = False
        Range("D40").Select
        ActiveSheet.Protect Password:="1"
    Else
        ActiveSheet.Protect Password:="1"
        Sheets("Sheet1").Range("D40").Locked = True
       
    End If
End Sub

Could you please give me some light?!

Thank you very much.

 

[vletm]

gui
Recheck Your
... Else ...
You seems to protect sheet before
change cells option
and after that ... nothing.

 

[gui]

gui
Recheck Your
... Else ...
You seems to protect sheet before
change cells option
and after that ... nothing.

Hi vletm,

Thank you very much for you input. Before seeing your reply, I changed the code to:

Private Sub Worksheet_Change(ByVal Target As Range)
   
    If Range("L40") = "1" Then
        ActiveSheet.Unprotect Password:="1"
        Range("D40").Locked = False
        Range("D40").Select
        ActiveSheet.Protect Password:="1"
    Else
        Range("M40").Select
        Range("D40").Locked = True
        ActiveSheet.Protect Password:="1"
         
    End If
End Sub

This made the If bit finally work, but the Else still doesn't happen (D40 still remains unlocked and selection doesn't change to M40 - which is an unprotected cell). Any other insight, please?

 

[gui]

I have now changed my code to the following:

Private Sub Worksheet_Change(ByVal Target As Range)

 

    If Range("L40") = "1" Then

        ActiveSheet.Unprotect Password:="1"

        Range("D40").Locked = False

        ActiveSheet.Protect Password:="1"

    ElseIf Range("L40") = "2" Then

        ActiveSheet.Unprotect Password:="1"

        Range("D40").Locked = True

        ActiveSheet.Protect Password:="1"

         

    End If

End Sub

Now, it does work, but not automatically. Weirdly enough, after changing the Yes/No option, I have to double-click onto an empty cell for the macro to load and be performed. Again, another insight? I thought this would be a 'simple' thing, but my knowledge is too limited to make it smooth, apparently.

 

[vletm]

gui
Your code runs every time then someone has changed something somewhere.
Could You change Your logic?

 

[gui]

gui
Your code runs every time then someone has changed something somewhere.
Could You change Your logic?

Thank you for replying to me again, vletm.

I do want my code to run every time someone clicks on Yes or No, hence changing the value on the linked cell. But that's the problem: it's not working. I change the selection from Yes to No (or No to Yes, whichever) then have to double-click on my spreadsheet for the macro to run. Is this a problem with my code or with my machine?!

Cheers

 

[vletm]

gui
As I tried to write with my the last line... every time ... somewhere.
Before You have sent a sample file of Your challenge
... I could continue to guess ... guess ...

 

[gui]

Problem solved. I right-clicked on the Yes/No button and assigned a Macro it. Worked like a charm.

Thanks a lot for the space, the availability and the help.

Cheers