Conditional Unlocking

Thank you very much. To make sure I'm doing this right...the sheet is protected, and b3:h14 are locked. I right click on the sheet, and go to 'View Code'. I then go to Insert->Module and copy and paste the code in there. Is there anything else I need to do to 'run' the subroutine?

Originally Posted by jaslake

Hi hektisk

I think this does as you requested

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A4:A14")) Is Nothing Then
        If Target.Value = "New Assessment (No DC)" Then
            ActiveSheet.Unprotect
            Target.Offset(0, 1).Resize(1, 7).Locked = False
            ActiveSheet.Protect
        Else
            ActiveSheet.Unprotect
            Target.Offset(0, 1).Resize(1, 7).Locked = True
            ActiveSheet.Protect
        End If
    End If
End Sub

Place the code in Sheet1 Module.

Let me know of issues.

OK, prior to your post I had put the code both in the worksheet module and general module just in case, and but neither seemed to work. I opened the workbook you sent me, enabled macros, and every cell from b3:h13 are locked, no matter the data in a3:a13. I see the text you inputted in b7:b9, so I'm not sure what I'm doing wrong. Is there something else I need to do? Thank you again.

Originally Posted by jaslake

Hi hektisk
Almost...right click on the sheet (Sheet1) based on your sample file...copy the code into the window on the right. The code DOES NOT go in a general module...it goes in the sheet module.
See attached.

Aha! It's working great now. Thank you so much!

One thing I'm noticing, however...since the sheet is now protected by default, new rows cannot be added to the table. It's vital that the table be expandable. I'm not sure if there's any way around this...unless if the worksheet ONLY becomes protected when one of the cells in b3:h13 (or beyond) is selected. Is this practical?

Originally Posted by jaslake

Hi hektisk

Ah...much better. Try the attached. I'm not certain where you're going with this. Let me know of issues.