lock cells based on another cell value inexcel

[chavanalini]

Hi,

please help to write VBA code to lock cells based on the other cell value.

In Sheet 1 from column A to F i have some data, based on the column F value i want to block the columns W,X,Y, means wherever the column F value is having "Died" I want to unlock the columns W,X,Y to enter some data, if it is not "Died" I want to lock columns W,X,Y and cursor has to move from column V to column Z

Please help me

Thanks & Regards

[buran]

Hi
something like this should do the job:
Put this in the sheet module where your data are.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cl As Range
    If Not Intersect(Columns("F:F"), Target) Is Nothing Then    'at least one cell in column F has changed
        Me.Unprotect 'Password:="Your_Password" 'Uncomment this if you have password protected sheet
        For Each cl In Target
            If cl.Column = 6 Then
                If cl.Value = "Died" Then
                    cl.Offset(0, 17).Resize(1, 3).Locked = False
                Else
                    cl.Offset(0, 17).Resize(1, 3).Locked = True
                End If
            End If
        Next cl
        Me.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True ', Password:="Your_Password" 'Uncomment this if you want password protected sheet
    End If
End Sub

Note:
1.The cells that will be editable should be unlocked in advance e.g. range A:F
2.cells in W:Y should be Locked unlocked properly in advance, i.e. if you have Died in F2, then W2:Y2 should be set to unlocked
3.After running the macro your sheet will be protected, without password and only cell that are unlocked will be editable and selectable.
2. Prot

[chavanalini]

Thank you for your reply,

column A to Column F columns are not editable why because those columns are auto generated from other sheet and user is not allowed to change those values. Please help me to write a code for without changing the values in column A to F

Please help me

Thanks in advance

[buran]

I don't understand your workflow. can you elaborated a little?
which cells should be editable and which - not. how it should lock/unlock the cells?

[chavanalini]

sorry for inconvenience,

I will explain clearly, In Sheet1 from column A to F are having some data (this is auto generated by other sheet) and it is locked. Based on the available data in column A to F user have to enter some data in other columns (G to Z) which are empty. but I want to lock columns W,X,Y wherever Column F is having other than "Died".

For example
F2 is having "X" user can enter data in columns from G to Z except W,X,Y (When user entered data in to column V immediately cursor has to move from V to Z)

F3 is having "Died" user can enter data in all columns from G to Z

Thank you so much for your help

[chavanalini]

sorry for inconvenience,

I will explain clearly, In Sheet1 from column A to F are having some data (this is auto generated by other sheet) and it is locked. Based on the available data in column A to F user have to enter some data in other columns (G to Z) which are empty. but I want to lock columns W,X,Y wherever Column F is having other than "Died".

For example
F2 is having "X" user can enter data in columns from G to Z except W,X,Y (When user entered data in to column V immediately cursor has to move from V to Z)

F3 is having "Died" user can enter data in all columns from G to Z

Thank you so much for your help

Please help me

[buran]

try this

Sub LockCells()
    Dim cl As Range
    If Not (ActiveSheet.UsedRange.Address = "$A$1" And Range("A1") = vbNullString) Then
        ActiveSheet.Unprotect    'Password:="Your_Password" 'Uncomment this if you have password protected sheet
        Columns("G:Z").Cells.Locked = False
        For Each cl In Intersect(Columns("F:F"), ActiveSheet.UsedRange)
            If cl.Value = "Died" Then
                cl.Offset(0, 17).Resize(1, 3).Locked = False
            Else
                cl.Offset(0, 17).Resize(1, 3).Locked = True
            End If
        Next cl
        ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True    ', Password:="Your_Passowrd" 'Uncommect this if you want password protected sheet
    End If
End Sub

[chavanalini]

Thank you so much for your reply, but it is not working

Please tell me where i have to copy this code and tell me which columns I have to lock and which columns I have to unlock before run this code

Thank you

[buran]

put it in a normal module.
it doesn't matter which cells are locked or unlocked. I unlock all cells in columns G:Z, then I lock the cells W:Y for which"value in F column is not "Died".
At the end user should be able to edit cells In G:V and Z for which you don't have Died and G:Z for these where F=Died

[chavanalini]

Thank you,

I inserted a new module and I pasted the code but its not working, I am attaching my excel file, please help me how to do this

Yellow colour columns has to be locked when the F column is not have "Died"

Thanks

[buran]

I think now this should work

Sub LockCells()
    Dim cl As Range
    If Not (ActiveSheet.UsedRange.Address = "$A$1" And Range("A1") = vbNullString) Then
        ActiveSheet.Unprotect    'Password:="Your_Password" 'Uncomment this if you have password protected sheet
        Range("G2:Z" & Rows.Count).Cells.Locked = False
        For Each cl In Intersect(Columns("F:F"), ActiveSheet.UsedRange)
            If cl.Value <> "Died" Then
                cl.Offset(0, 17).Resize(1, 3).Locked = True
            End If
        Next cl
        With ActiveSheet
            .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True    ', Password:="Your_Passowrd" 'Uncommect this if you want password protected sheet
            .EnableSelection = xlUnlockedCells
        End With
    End If
End Sub

I assume for all cells Locked property is TRUE, which is the default value. The problem was due to the merged cells in row 1

[chavanalini]

sorry for troubling you it is not working,

If you dont mind can you put this code in to my excel file and see whether it is working or not, if it is working please forward the same file to me

sorry for troubling you so much

and thank you so much for you timely help

thanks

[buran]

here it is. run macro LockCells

[chavanalini]

Thank you so much it is working, one small doubt whenever I will open excel file is it i have to run macro?

[buran]

you should run macro after you add/update values in A:F

[chavanalini]

Ok but it is little difficult to user to run macro every time is there any other way to do this

[buran]

i give up. sorry...
it's either user run macro after data are extracted from the DB, or like my first suggestion which you didn't like, if data on worksheet change.

[chavanalini]

Thank you so much i will take your first suggestion only