vba Excel - 基于值锁定单元格范围

Excel - Lock Range of Cells Based on Values

提问by Mus

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.

“是/否”单元格实际上是两个单元格（G13 和 H13）的合并。我已经更新了我的例子来反映这一点。

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.

如果用户选择“否”，那么我希望锁定问题范围的其余部分 (G13-H13:AB13)，因为无需在此处输入数据。然而，如果用户选择“是”，则剩余的单元格将保持可用于输入数据。

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.

再次强调，所有数据都是通过下拉菜单输入的，不需要手动输入任何内容；我希望这样，如果G13-H13 = "No"，那么具有下拉列表的范围内的其余单元格被阻止或锁定，无法从各自的下拉列表中选择更多信息。

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

请注意，G13-H13 中的值可以是“是”或“否”。

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

这可以使用 VBA 实现吗，如果可以，如何实现？

Many thanks.

非常感谢。

回答by Jon Crowell

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

编辑：您可以在没有 VBA 的情况下执行此操作。我基于这里的另一个答案：https: //stackoverflow.com/a/11954076/138938

Column G has the Yes or No drop-down.

G 列具有“是”或“否”下拉列表。

In cell H13, set up data validation like this:

在单元格 H13 中，像这样设置数据验证：

1. Data --> Data Validation
2. Select Listin the Allowdrop-down.
3. Enter this formula in the Sourcefield: =IF($G13="Yes", MyList, FALSE)
4. Copy cell H13 and paste the validation (paste --> pastespecial --> validation) to cells I13:AB13.
5. Replace MyListin 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.

1. 数据 --> 数据验证
2. 在允许下拉列表中选择列表。
3. 在源字段中输入此公式：=IF($G13="Yes", MyList, FALSE)
4. 复制单元格 H13 并将验证（paste --> pastespecial --> validation）粘贴到单元格 I13:AB13。
5. 将MyList公式中的列表替换为您希望允许用户从每一列中进行选择的列表。请注意，您需要将患者回答设置为“是”才能设置验证。设置后，您可以将其删除或将其设置为否。
6. 为第 13 行设置验证后，将验证复制并粘贴到所有需要它的行。

---

---

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.

如果要使用 VBA，请使用以下内容，并使用 worksheet_change 事件或其他一些机制来触发 LockOrUnlockPatientCells。我不喜欢使用 worksheet_change，但在这种情况下它可能有意义。

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

回答by Scott Holtzman

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.

将模块放置在 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

回答by Rohit Agrawal

Try this : -

尝试这个 ： -

Apply a Loop and then check

应用循环然后检查

if "NO"

如果不”

ws.get_Range(StartCell, EndCell).Locked = true;

ws.get_Range(StartCell, EndCell).Locked = true;

// where ws is the worksheet object The only point to be seen is that if the Sheet is Locked you need to unlock it and then proceed

// 其中 ws 是工作表对象唯一要看到的是，如果工作表被锁定，您需要解锁它然后继续

else if "YES"

否则如果“是”

continue;

继续;