Locking an Excel Cell

[OKearney]

I am creating a program within Excel where the workbook will be used by multiple players. Each player will be on their own sheet within the Workbook itself. Each sheet references answers selected in others sheets. Part of the program prompts users to select an answer from a drop down menu, so either "red" or "blue". The thing is that I want the cell to lock after the user selects an answer so that they cannot go back and change it. This is important because the answer they select determines how much money they will end up making at the end of the program. There is an incentive for users to change answers, so I want to eliminate this possibility by having cells automatically lock after an option is selected. I am very unfamiliar with how to create a macro so any help would be greatly appreciated!

 

[Smitty]

Welcome to the Board!

You can use a Change event. Here's an example:

<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("A1")<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>            ActiveSheet.Unprotect<br>                Target.Locked = <SPAN style="color:#00007F">True</SPAN><br>            ActiveSheet.Protect<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

To use event code, you can right-click on the worksheet tab where you want the code to run, then select View Code and paste the code in the new window that opens.

HTH,

 

[OKearney]

How would it take into consideration that the cell only locks after "Red" or "Blue" is selected? VBA is like a foreign language to me, so I am still a bit confused!

 

[Smitty]

What I posted doesn't care what's entered, only that the cell is changed. If you want to limit the locking to Red or Blue selections you'd need to add a qualifier.

            If Target.Value = "Red" Or Target.Value = "Blue" Then
                ActiveSheet.Unprotect
                    Target.Locked = True
                ActiveSheet.Protect
            Else
                Exit Sub
            End If

 

[OKearney]

Thank you!!

 

[OKearney]

So apparently I still am not quite figuring it out.
This is what I have entered:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = Target.Parent.Range("C6") ---> Excel is highlighting this for some reason
If Target.Count > 1 Then Exit Sub
If Target.Value = "Red" Or Target.Value = "Blue" Then
ActiveSheet.Unprotect
Target.Locked = True
ActiveSheet.Protect
Else
Exit Sub
End If
End Sub

When I test the cell, it does not lock after selecting red or blue...