CONDITIONAL LOCKING - Please Help!

[KLetters]

I need help with a macro so that simply typing in an X in a cell will password protect the entire spreadsheet.
My boss wants me to have our purchase orders lockup once he puts in the PO information and puts his digital signature, he wants to type in an X in a cell and have the entire thing become password protected..
Lets say the cell is E41.
Can you please help me with the Macro.
I would be very grateful.
Thank you veru much
Kevin

 

[Smitty]

Welcome to the Board!

Something like this maybe:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br><SPAN style="color:#007F00">'   Code goes in the Worksheet specific module</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#007F00">'   Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> rng = Target.Parent.Range("E41")<br>        <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>        <SPAN style="color:#007F00">'   Only look at that range</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>        <SPAN style="color:#007F00">'   Action if Condition(s) are met (do your thing here...)</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> UCase(Target.Value) = "X" <SPAN style="color:#00007F">Then</SPAN><br>                ActiveSheet.Protect "abc"<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

 

[KLetters]

Welcome to the Board!

Something like this maybe:

Private Sub Worksheet_Change(ByVal Target As Range)
' Code goes in the Worksheet specific module
Dim rng As Range
' Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")
Set rng = Target.Parent.Range("E41")
' Only look at single cell changes
If Target.Count > 1 Then Exit Sub
' Only look at that range
If Intersect(Target, rng) Is Nothing Then Exit Sub
' Action if Condition(s) are met (do your thing here...)
If UCase(Target.Value) = "X" Then
ActiveSheet.Protect "abc"
End If
End Sub

HTH,

Thanks Smitty for replying so quickly, I will admit Im a novice as far as programming a worksheet.
Im not sure which parts I should adjust, even with your idiot proof notes.
Also where you wrote "abc" is that the password I should change to my password in the quotes?
When I put the X in nothing locks, Im sure Im doing something wrong.
Thanks again for your help.
Kevin

 

[Smitty]

Where did you put the code? It should go in the worksheet specific module, which you can get to by right-clicking the sheet tab-->View Code.

As for the "abc" part, yes, that's where your password would go if you have one.

 

[KLetters]

Perfect thank you Smitty, youre the man.
Just one question, even after it is protected can someone go into the code and change the password?

 

[Smitty]

Just one question, even after it is protected can someone go into the code and change the password?

Yes, but if you go into the VBE you can goto Tools-->VBA Project Properties and protect it.