Excel - Lock Range of Cells Based on Values

Question

Is it possible to lock a particular range of cells based on the input from a dropdown in a row of data?

For example, each row of my spreadsheet represents a patient, and the first cell asks a question, to which a "Yes" or "No" response is required (which is selected/entered via a dropdown).

EDIT

The "Yes/No" cell is, in fact, a merge of two cells (G13 & H13). I have updated my example to reflect this.

EDIT ENDS

If the user selects "No", then I wish to lock the remainder of the range of questions (G13-H13:AB13) as there is no need to enter data here. However, if the user selects, "Yes", then the remainder of the cells shall remain available to enter data into.

All cells within each range have data entered via dropdowns only.

Here is what I am hoping to achieve:

If "No"
    Then lock range G13-H13:AB13
Else If "Yes"
    Then do nothing

i.e.

 G13-H13  I13-J13  K13-L13   ....     ....     AB13
|  NO   |  ----  |  ----  |  ----  |  ----  |  ----  |  (Locked Cells)

OR

 G13-H13  I13-J13  K13-L13   ....     ....     AB13
|  YES  |        |        |        |        |        |  (Unlocked Cells)

Once again, I would like to emphasize that all data is entered via dropdown menus and that nothing is to be typed in manually; I would like it so that if G13-H13 = "No", then the remainder of the cells within the range which have dropdowns are blocked or locked from having further information selected from their respective dropdowns.

Please note that the value in G13-H13 can be either "Yes" or "No".

Can this be achieved using VBA and if so, how?

Many thanks.

Answer 1

EDIT: You can do this without VBA. I based this on another answer here: https://stackoverflow.com/a/11954076/138938

Column G has the Yes or No drop-down.

In cell H13, set up data validation like this:

1. Data --> Data Validation
2. Select List in the Allow drop-down.
3. Enter this formula in the Source field: =IF($G13="Yes", MyList, FALSE)
4. Copy cell H13 and paste the validation (paste --> pastespecial --> validation) to cells I13:AB13.
5. Replace MyList in the formula with the list you want to allow the user to select from for each column. Note that you'll need to have the patient answer set to "Yes" in order to set up the validation. Once you set it up, you can delete it or set it to No.
6. Once you have the validation set up for row 13, copy and paste the validation to all rows that need it.

---

If you want to use VBA, use the following, and use the worksheet_change event or some other mechanism to trigger the LockOrUnlockPatientCells. I don't like using worksheet_change, but it may make sense in this case.

In order for the cells to be locked, you need to lock them and then protect the sheet. The code below does that. You need to pass it the row for the patient that is being worked on.

Sub LockOrUnlockPatientCells(PatientRow As Long)
    Dim ws As Worksheet
    Dim YesOrNo As String

    Set ws = ActiveSheet
    YesOrNo = ws.Range("g" & PatientRow).Value

    ' unprotect the sheet so that we can modify locked settings
    ws.Unprotect
    ws.Range("a:g").Cells.Locked = False

    ' lock row
    Range("h" & PatientRow & ":AB" & PatientRow).Cells.Locked = True

    ' unlock the row depending on answer
    If YesOrNo = "Yes" Then
        Range("h" & PatientRow & ":AB" & PatientRow).Cells.Locked = False
    End If

    ' protect the sheet again to activate the locked cells
    ws.Protect

End Sub

You can test the functionality by using the following, manually adjusting the values for the patient row. Once you get it to work the way you want, get the row from the user's input.

Sub testLockedCells()
    Dim AnswerRow As Long

    AnswerRow = 9

    LockOrUnlockPatientCells AnswerRow
End Sub

Answer 2

This code should get you started. You may need to tweak it to suit your specific needs, but I based my logic on the details in your original post.

Place the module in the respective Worksheet Object in the VBE.

Private Sub Worksheet_Change(ByVal Target As Range)


'assumes cell changed in column G and first row of data entry is 13
If Target.Column = 7 And Target.Row > 12 Then 'change to If Intersect(Target, Range("G13")) Then if all you care about is G13


    Application.EnableEvents = False 'stop events from processing

    Dim blnLock As Boolean
    Dim r As Integer

    Select Case Target.Value
        Case Is = "No"
            blnLock = True
            r = 191
        Case Is = "Yes"
            blnLock = False
            r = 255
    End Select

    Unprotect Password:="myPassword"

    With Range("H" & Target.Row & ":AB" & Target.Row)

        'just a suggestion, fill the cells grey before locking them, or turn them back to no fill if it's unlocked
        .Interior.Color = RGB(r, r, r)
        .Locked = blnLock

    End With

    .Protect Password:="myPassword"

    Application.EnableEvents = True

End If


End Sub