Worksheet change help needed

[TheWennerWoman]

I have a worksheet which contains seven dropdown boxes.

There are two asks I am struggling with.

Firstly, if the user selects "At fault" in cell B16 then I need cell B21 to populate with "Yes" and cells B22-B26 to be locked. If the user subsequently selects something different in cell B16 then cell B21 is cleared and cells B22-B26 become unlocked.

Secondly, if the user selects anything other than "At fault" in cell B16, cell B21 is locked and they can't select it (they can only select cells B22-B26).

I have been trying worksheet change events with "Target Range" and "Application Intersect" but can't get it to work.

Any advice greatly appreciated as always.

 

[Joe4]

I think your explanation may be missing a few details such as:
- should cell B21 become unlocked in some scenarios?
- should cell B21 be cleared out in any scenarios?

In any event, that can easily enough be added to the code below.

Note before you use this code, you must remove the "Locked" property of all cells.
You can do this by selecting all cells, then right-click on any cell and select "Format Cells"
Go to the "Protection" tab, and uncheck the "Locked" box.

Then, this Worksheet_Change code should do what you want:

Private Sub Worksheet_Change(ByVal Target As Range)

'   Check to see if cell B16 updated, and if not, exit sub
    If Intersect(Target, Range("B16")) Is Nothing Then Exit Sub
   
    Application.EnableEvents = False
   
'   Check value of cell B16
    Select Case Range("B16").Value
'       If "At fault" is entered in cell B16
        Case "At fault"
'           Set value of cell B21
            Range("B21") = "Yes"
'           Lock cells B22:B26
            ActiveSheet.Unprotect
            Range("B22:B26").Locked = True
            ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
'       If anything else entered in cell B16
        Case Else
'           Lock cell B21 and unlock cells B22:B26
            ActiveSheet.Unprotect
            Range("B21").Locked = True
            Range("B22:B26").Locked = False
            ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    End Select
   
    Application.EnableEvents = True

End Sub

 

[TheWennerWoman]

Thanks for that, much appreciated.

Upon opening, all cells will be unlocked.

Cell B21 only needs to be locked if the user selects anything other than "At fault" in cell B16.

 

[Joe4]

Cell B21 only needs to be locked if the user selects anything other than "At fault" in cell B16.

Right, but does it actually need to become unlocked if cell B16 is "At fault"?
I wasn't sure, as you are already populating it with a "Yes" if B16 is set to "At fault".

Anyway, did you try the code and see how it works for you?

 

[TheWennerWoman]

Right, but does it actually need to become unlocked if cell B16 is "At fault"?
I wasn't sure, as you are already populating it with a "Yes" if B16 is set to "At fault".

Anyway, did you try the code and see how it works for you?

I'll give it a go this afternoon, many thanks again. I will feedback.

 

[TheWennerWoman]

Right, but does it actually need to become unlocked if cell B16 is "At fault"?
I wasn't sure, as you are already populating it with a "Yes" if B16 is set to "At fault".

Anyway, did you try the code and see how it works for you?

Thanks for your help, everything works the way I need it to