Lock range of cells depending on value in final cell in range

[ketarix]

Hi,

Firstly let me state that I am not proficient with VBA, I need spoon feeding here I am afraid so please be my saviour(s)!

The issue I am having is that I am trying to create a medical notes sheet where the first lot of cells lock once a value is entered in a specific cell.

I need this to work either indefinitely, or to a very high number of rows down.

So Columns "A" to "I" need to lock once "I" has "Yes" entered in it. I have found macros to lock the entire row, but I have further columns beyond I that which need to stay unlocked.

I am password protecting the sheet, as this seems to be a necessity if my failed attempt to find a solution is anything to go by.

Save me!

 

[Fishboy]

Hi,

Firstly let me state that I am not proficient with VBA, I need spoon feeding here I am afraid so please be my saviour(s)!

The issue I am having is that I am trying to create a medical notes sheet where the first lot of cells lock once a value is entered in a specific cell.

I need this to work either indefinitely, or to a very high number of rows down.

So Columns "A" to "I" need to lock once "I" has "Yes" entered in it. I have found macros to lock the entire row, but I have further columns beyond I that which need to stay unlocked.

I am password protecting the sheet, as this seems to be a necessity if my failed attempt to find a solution is anything to go by.

Save me!

Hi ketarix,

For this to work you will first need to select ALL cells-->Format Cells-->Protection-->Untick "Locked". This is because otherwise the moment you protect the sheet ALL cells will be protected. Once you have done that you can try the following Worksheet_Change macro. Try it out in a COPY of the workbook. Right-click on the tab name and select View Code. In the new window that opens simply copy / paste my code in. The comments in the code should explain what it is doing. Update the bold red part with the correct password:

Private Sub Worksheet_Change(ByVal Target As Range)
' If your target is in column I then...
If Not Intersect(Target, Range("I:I")) Is Nothing Then
    ' If the target value is Yes (case insensitive) and A:I of the target row are all filled then...
    If UCase(Target.Value) = "YES" And Application.WorksheetFunction.CountA(Range("A" & Target.Row, "I" & Target.Row)) = 9 Then
        ' Unprotect the sheet with the password
        ActiveSheet.Unprotect "PASSWORD"
        ' Format A:I of the target row as "Locked"
        Range("A" & Target.Row, "I" & Target.Row).Locked = True
        ' Reprotect the sheet with the password
        ActiveSheet.Protect "PASSWORD"
    End If
End If


End Sub