Excel - 基于值的单元格锁定范围 [英] Excel - Lock Range of Cells Based on Values

问题描述

是否可以根据数据行中的下拉列表的输入来锁定特定范围的单元格？

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).

编辑

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

编辑结束

如果用户选择否，则我希望锁定其余的问题范围（G13-H13：AB13），因为这里不需要输入数据。然而，如果用户选择是，则剩余的单元格将保持可用以输入数据。

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)

再一次，我想强调，所有数据都是通过下拉菜单输入的，没有什么是手动输入的;我想要这样，如果 G13-H13 =否，则具有下拉列表范围内的其余单元格被阻止或锁定，

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中的值可以是是还是否。

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

可以使用VBA来实现吗？如果是这样，那么如何？

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

非常感谢。 b $ b

Many thanks.

推荐答案

编辑：
无需VBA即可。我基于另一个答案： https://stackoverflow.com/a/11954076/138938

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

Column G has the Yes or No drop-down.

在单元格H13中，设置如下所示的数据验证：

In cell H13, set up data validation like this:

1. 数据 - - >数据验证


2. 在允许下拉列表中选择列表。


3. 输入源字段中的此公式： = IF（$ G13 =是，MyList，FALSE）


4. 复制单元格H13并将验证（粘贴 - >粘贴 - >验证）粘贴到单元格I13：AB13。


5. 替换 MyList 在您想允许用户从每个列中选择列表的公式中。请注意，为了设置验证，您需要将患者答案设置为是。一旦设置完毕，您可以将其删除或将其设置为否。


6. 对第13行进行验证设置后，将验证复制并粘贴到所需的所有行。

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.

---

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

---

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

这篇关于Excel - 基于值的单元格锁定范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！