• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Unlock password protected cell based on another cell

gui

New Member
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:

Code:
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.
 
Last edited by a moderator:

vletm

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

gui

New Member
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:

Code:
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

New Member
I have now changed my code to the following:


Code:
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.
 

gui

New Member
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

Excel Ninja
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

New Member
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
 
Top