Excel 相关下拉列表（带/不带 VBA）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6151165/
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
Excel dependent dropdown lists (With/without VBA)
提问by Jort
I'm completely new to VBA, even though I'm quite familiar with regular VB. I don't know if this has to be done through VBA, or if it can be done with built-in Excel functionalities. Basically, I have a bunch of data in columns on a hidden sheet. There should be two dropdown lists on another sheet, one of which depends on the other's selected value. There are three entities in the relationship though (see example below) I have no idea how I get this to work, or even how the relationships between these rows would work.
我对 VBA 完全陌生，尽管我对常规 VB 非常熟悉。我不知道这是否必须通过 VBA 来完成，或者是否可以使用内置的 Excel 功能来完成。基本上，我在隐藏工作表上的列中有一堆数据。另一张纸上应该有两个下拉列表，其中一个取决于另一个选择的值。尽管关系中有三个实体（请参见下面的示例），但我不知道如何使其工作，甚至不知道这些行之间的关系将如何工作。
Here's an example of exactly what I want to achieve: There are two columns on one sheet: "Employee" and "Project". Employee contains a set dropdown list of employees. This data is located in a hidden sheet. Every employee is linked to a single department, and each department is linked to a number of projects. Once the user has selected a particular employee, I want the "Project" column of the active row to fill up with a dropdown list containing all projects that are linked to the department the selected employee belongs to. How would I go about setting these relations in Excel, and write the VBA function that makes this functionality happen?
这是我想要实现的确切目标的示例：一张纸上有两列：“员工”和“项目”。员工包含一组员工下拉列表。此数据位于隐藏的工作表中。每个员工都链接到一个部门，每个部门都链接到多个项目。一旦用户选择了特定员工，我希望活动行的“项目”列填充一个下拉列表，其中包含链接到所选员工所属部门的所有项目。我将如何在 Excel 中设置这些关系，并编写实现此功能的 VBA 函数？
I can't provide you with an example of what the data columns on the hidden sheet look like, since I have no idea how I should arrange them to begin with.
我无法为您提供隐藏工作表上数据列的示例，因为我不知道应该如何安排它们。
回答by Doc Brown
Here you find a complete tutorial how to do this:
在这里您可以找到如何执行此操作的完整教程：
http://www.contextures.com/xldataval02.html
http://www.contextures.com/xldataval02.html
You don't need VBA for this as long as the user of your sheet does not change the data which is allowed in your dropdown lists. If this is the case, I suggest you should add some kind of "update" functionality (using VBA). This update function has just to redefine the named cell area you provide for each of your dropdown lists whenever your allowed data changes.
只要您的工作表用户不更改下拉列表中允许的数据，您就不需要 VBA。如果是这种情况，我建议您应该添加某种“更新”功能（使用 VBA）。此更新功能只需在您允许的数据更改时重新定义您为每个下拉列表提供的命名单元格区域。
Changing the area is a one-liner in VBA, as far as you know the area (firstRow, lastRow, column) where the data resides:
改变区域是VBA中的一个单行，据你所知数据所在的区域( firstRow, lastRow, column)：
ThisWorkbook.Names.Add Name:="areaname", _
RefersToR1C1:="=YourSheetName!R" & firstRow & "C" & column _
& ":R" & lastRow & "C" & column
回答by Jean-Fran?ois Corbett
This works:
这有效：
I assumed three departments (A,B,C) and projects numbered 1 to 9. There are three employees. Of course, this can be generalized to any number of employees, depts, projects.
我假设了三个部门（A、B、C）和编号为 1 到 9 的项目。有三名员工。当然，这可以推广到任意数量的员工、部门、项目。
I placed the data like this:
我这样放置数据：
ColA ColB ColC ColD ColE ColF ColG
Name Dept Dept A B C
Peter A Projects 1 4 8
Paul B 2 5 9
Mary C 3 6
7
In cell A7, I have a drop-down menu created using data validation, in which you can choose the employee:
在单元格 A7 中，我有一个使用数据验证创建的下拉菜单，您可以在其中选择员工：
- Data-->Validation-->List-->Source =$A$2:$A$4
- 数据-->验证-->列表-->来源=$A$2:$A$4
In the sheet module:
在工作表模块中：
Private Sub Worksheet_Change(ByVal Target As Range)
Call FillDropDown
End Sub
which automatically updates the drop-down menu in cell D7, from which you can then choose the project:
它会自动更新单元格 D7 中的下拉菜单，然后您可以从中选择项目：
Sub FillDropDown()
Dim dept As String
Dim col As Long
dept = WorksheetFunction.VLookup(Range("A7"), Range("A2:B4"), 2, False)
col = WorksheetFunction.Match(dept, Range("E1:G1"), 0)
With Range("D7").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$" & Chr(68 + col) _
& ":$" & Chr(68 + col) & "" 'Range("E2:E4").Offset(0, col - 1)
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub
This should provide you with something to start with. Adjust cell addresses as appropriate, to refer to your hidden sheet, to varying numbers of employees, depts, projects, etc.
这应该为您提供一些开始。根据需要调整单元格地址，以引用您隐藏的工作表、不同数量的员工、部门、项目等。
