vba 根据另一个单元格中的值在一个单元格中创建数据验证
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21761822/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Create a data validation in a cell based on the value in another cell
提问by ewitkows
I have 2 columns in a spreadsheet, both are picked from a drop down list.
我在电子表格中有 2 列，都是从下拉列表中选取的。
Col A is "Do you want more information" - Possible Values Yes\No
Col A 是“您是否需要更多信息”- 可能的值是\否
Col B is "How did you hear about us" - Possible values are Email\Phone\Other
Col B 是“您是如何听说我们的” - 可能的值是电子邮件\电话\其他
Question: If "No" is chosen in Col A, then Col B's value should be N/A, otherwise the user should be able to choose from the dropdown and pick a value. Using the following formula, I can get N/A to appear, but the "false" option overwrites any available dropdown values or previously selected values. So simply put, if Yes is chosen, let user choose a value in the other cell. If No is chosen, show N/A in the other cell. Thoughts on how I can get this to work?
问题：如果在 Col A 中选择“否”，则 Col B 的值应该是 N/A，否则用户应该能够从下拉列表中选择并选择一个值。使用以下公式，我可以让 N/A 出现，但“false”选项会覆盖任何可用的下拉值或先前选择的值。所以简单地说，如果选择 Yes，让用户在另一个单元格中选择一个值。如果选择否，则在其他单元格中显示 N/A。关于如何让这个工作的想法？
My current formula that doesn't work (which resides in cell A2):
我当前的公式不起作用（位于单元格 A2 中）：
=IF(A1="No","N/A","Select Value")
回答by Jerome Montino
Try this:
尝试这个：
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
If Target.Column = 1 Then
Set Cell = Target.Offset(0, 1)
If Len(Target.Value) = 0 Then
Cell.Validation.Delete
Cell.Value = vbNullString
Else
If Target.Value = "Yes" Then
With Cell.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="=ContactMethod"
End With
ElseIf Target.Value = "No" Then
Cell.Validation.Delete
Cell.Value = "N/A"
Else
MsgBox "Input only Yes or No."
Target.ClearContents
Cell.Validation.Delete
End If
End If
End If
End Sub
Screenshot:
截屏：
If Yesis entered in Column A (note the dropdown arrow):
如果Yes在 A 列中输入（注意下拉箭头）：
If Nois entered in Column A (note the now missing dropdown arrow):
如果No在 A 列中输入（注意现在缺少的下拉箭头）：
If anything else is entered in Column A:
如果在 A 列中输入了其他内容：
In addition, it safely deletes the validation if Column A's value is deleted or changed to No. This way, there's no way to access the dropdown unless Column A is specifically Yes.
此外，如果 A 列的值被删除或更改为 ，它会安全地删除验证No。这样，除非 Column A 特别是 ，否则无法访问下拉列表Yes。
Make sure to paste it in the sheet's module (Sheet9 in the screenshot below):
确保将其粘贴到工作表的模块中（以下屏幕截图中的 Sheet9）：
Let us know if this helps.
如果这有帮助，请告诉我们。
回答by CRondao
Just for the fun of it, because it is not a complete solution, that will need a litle vba... Firs ComboBox in A1 (Yes/No), in D1:D3 (Email/Phone/Other), in E1 (NA). Write as source for B1 list Data Validation:
只是为了它的乐趣，因为它不是一个完整的解决方案，需要一点 vba ...... A1（是/否），D1：D3（电子邮件/电话/其他），E1（NA）中的 Firs ComboBox ）。写入 B1 列表数据验证的源：
=CHOOSE(IF(A1="Yes";1;2);D1:D3;E1)
You need VBA, for, at least, delete the value in B1 when the value in A1 changes, and change it to "NA" if A1=No
您需要VBA，因为至少在A1中的值发生变化时删除B1中的值，如果A1 = No，则将其更改为“NA”
回答by Muddler
This is the solution for my task. The code lets us change the data validation in one cell depending on the datavalidated input in the previous one.
这是我的任务的解决方案。该代码允许我们根据前一个单元格中的 datavalidated 输入更改一个单元格中的数据验证。
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("L21")) Is Nothing Then
Worksheets(3).Range("L23").Clear
Select Case Range("L21")
Case "x": x_projection
Case "y": y_projection
Case "z": z_projection
End Select
End If
End Sub
Sub x_projection()
Worksheets(3).Range("L23").Validation.Delete
Worksheets(3).Range("L23").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="y, z"
Worksheets(3).Range("L23") = "z"
End Sub
Sub y_projection()
Worksheets(3).Range("L23").Validation.Delete
Worksheets(3).Range("L23").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="x, z"
Worksheets(3).Range("L23") = "x"
End Sub
Sub z_projection()
Worksheets(3).Range("L23").Validation.Delete
Worksheets(3).Range("L23").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="x, y"
Worksheets(3).Range("L23") = "y"
End Sub
