Lock/Unlock Cells Based On Validation Lists

Re: Lock/Unlock Cells Based On Validation Lists

Hi Ana,

As you wrote "remember I'm using Excel 2010" I don't know if this is what you need (using Excel 2003 version).

Private Sub Worksheet_Change(ByVal Target As Range)


    If Not Intersect(Target, Range("F11:F20")) Is Nothing Then
            
            ActiveSheet.Unprotect
               
        If Target.Value = "Active" Then
            Range("J" & Target.Row & ":P" & Target.Row).Select
            Selection.Locked = False
        Else
            Range("J" & Target.Row & ":P" & Target.Row).Select
            Selection.Locked = True
        End If
    End If
    
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True


End Sub

Success

Erik

Re: Lock/Unlock Cells Based On Validation Lists

VBA is subject to users choosing to enable macros - which they can elect not to do and thus defeat the code.

First - there is no validation list in cell F11 (or other cells) and the actual cell containing the value is F10 - you have the cells merged (bad idea - causes problems).
However, you can accomplish this without the use of VBA by using Conditional Formatting and Data Validation in conjunction with a helper cell.

Using column-Z as the help column, you could enter this formula: =F10="On Leave"
Which will return TRUE or FALSE; TRUE = F10 contains "On Leave"; FALSE = F10 does not contain it

Set cells J10:P11 to use Conditional Formatting, formula option, using this formula; =$Z10=TRUE
Select a gray fill color
Cells J10:P11 will be colored gray if Z10 = TRUE due to F10 containing "On Leave"

Next . . .
Apply Data Validation to cells J10:P11, using the Custom option and use this formula: =$Z10=FALSE
Set the Error Alert style to Stop and create a custom message if desired.
When Z10 = False it means cells F10 contains "Active" so users can make entries. If Z10 = False then cell F10 contains "On Leave" and Data Validation will prevent users from making entries.

Re: Lock/Unlock Cells Based On Validation Lists

I applied this to my spreadsheet, but somehow when the cells are supposed to be cleared of the conditional formatting, only the top cell clears and the rest stays black (color I used).

I did all of it just the same.

except

=A1>1
=A2>1

then for range =$A$3:$E$24
=G1=FALSE then it colors everything black. then I clicked the box Stop if True. when the cell a1 has data, it only clears a3 and the rest stays black

Re: Lock/Unlock Cells Based On Validation Lists

errtu,

Welcome to Ozgrid.

However, posting your question in a thread started by someone else is against Forum Rules (see link in my signature) and is known as thread hijacking.
Always start your own thread and, if it helps, you can provide a link back to this (or any other) post you think helps clarify your thread.

Please start your own thread and be sure to create a thread title that concisely and accurately describes YOUR thread content and is "search friendly", meaning YOUR title used as the search terms will yield good results.

Thanks,