Locking specific cell

[OKearney]

I have already asked a similar question but I am still not getting the results I had hope for. My goal is to have for instance an entire worksheet that is locked so that other users cannot change anything. However, there will be an exception of cells like C6, C12, C18, etc.. In these particular cells there will be a drop down tab where users can select either "red" or "blue". I want those specific cells to be unlocked UNTIL a user selects one of the options and then it has to lock. So basically it will only lock once either red or blue is selected. I have been playing around with it but I can only get it to the point where once any change is made the entire workbook locks, which defeats the purpose. This is what I had written so far which isn't working:
Private Sub Worksheet_Change(ByVal Target As Range) Dim cel As Range
ActiveSheet.Unprotect ' Password:="secret"
For Each cel In Target
If cel.Value = "Red" Or cel.Value = "Blue" Then
cel.Locked = True
Else
cel.Locked = False
End If
Next cel
ActiveSheet.Protect ' Password:= "secret"
End Sub

 

[cerfani]

just fyi, you can also write it this way...

cel.Locked = [/COLOR][COLOR=#333333]cel.Value = "Red" Or cel.Value = "Blue"[/COLOR]

 

[cerfani]

ok i need to remember not to use code tags on here, they dont work properly...


cel.Locked = cel.Value = "Red" Or cel.Value = "Blue"

 

[OKearney]

How would excel know to make this work only for this specific cell if all other cells are already locked?

 

[cerfani]

you check if it intersects with the specific cell...

If Not Intersect(Target, Range("A1")) is Nothing Then
'Target includes cell A1
End If

or just check the address directly...

If Target.Address = "$A$1" Then
'Target is cell A1
End If

the 2nd way i think is better because the first one will trigger true if more than one cell is selected

 

[cerfani]

not to mention locked cells with the sheet protected can't be edited so they won't call your event regardless... only unlocked cells can call a change event when the sheet is protected

 

[OKearney]

Ok sorry I am a complete beginner when it comes to excel and VBA so I need a bit of paint by numbers here. Once I get into the workspace to create a macro what is it exactly that I need to write? So far I have:

Sub Lockcell()
cel.Locked = cel.Value = "red" Or cel.Value = "blue"
End Sub

So where would I add in that other part? (Again... completely new to all of this so it is like a foreign language)

 

[cerfani]

try this...

Private Sub Worksheet_Change(ByVal Target As Range)

    'since your sheet is locked only unlocked cells will call this
    For Each cell In Target
    
        If cell.Value = "Red" Or cell.Value = "Blue" Then
            ActiveSheet.Unprotect ' Password:="secret"
        
            cell.Locked = True
        
            ActiveSheet.Protect ' Password:= "secret"
        End If
    
    Next cell
    
End Sub

since unlocked cells only can call this, you only need to decide wheteher or not to lock, so i take no action if the value is not red or blue

just add this to your worksheet code

 

[OKearney]

I wrote in exactly that and it still lets me change the cell between the two values...

 

[cerfani]

I just tested and seemed to work ok, this is what I did...

1. Opened a blank workbook
2. Unlocked cell A1
3. Then I protected the sheet
4. Opend VBA Editor and pasted the code above into the worksheet code
5. Then I typed Red into A1 (this called the macro and locked the cell)
6. Then I tried to type Blue into A1 and I got the warning that the cell was locked and couldn't be edited.

I am guessing you pasted the change event into a module and not the worksheet code