Excel 相关下拉列表(带/不带 VBA) [英] Excel dependent dropdown lists (With/without VBA)

问题描述

我对 VBA 完全陌生，尽管我对常规 VB 非常熟悉.我不知道这是否必须通过 VBA 来完成，或者是否可以使用内置的 Excel 功能来完成.基本上，我在隐藏工作表上的列中有一堆数据.另一张纸上应该有两个下拉列表，其中一个取决于另一个选择的值.尽管关系中有三个实体(请参见下面的示例)我不知道我是如何让它工作的，甚至不知道这些行之间的关系是如何工作的.

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.

这是我想要实现的目标的示例:一张纸上有两列:员工"和项目".员工包含一组员工下拉列表.此数据位于隐藏的工作表中.每个员工都链接到一个部门，每个部门都链接到多个项目.一旦用户选择了特定员工，我希望活动行的项目"列填充一个下拉列表，其中包含链接到所选员工所属部门的所有项目.我将如何在 Excel 中设置这些关系，并编写实现此功能的 VBA 函数?

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?

我无法举例说明隐藏工作表上的数据列是什么样的，因为我不知道应该如何安排它们.

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.

推荐答案

这里有完整的教程:

http://www.contextures.com/xldataval02.html

只要您的工作表用户不更改下拉列表中允许的数据，您就不需要 VBA.如果是这种情况，我建议您应该添加某种更新"功能(使用 VBA).此更新功能只需在允许的数据更改时重新定义您为每个下拉列表提供的命名单元格区域.

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中是单行的，就你所知的区域(firstRow,lastRow,column)在哪里数据驻留:

Changing the area is a one-liner in VBA, as far as you know the area (firstRow, lastRow, column) where the data resides:

  ThisWorkbook.Names.Add Name:="areaname", _
       RefersToR1C1:="=YourSheetName!R" & firstRow & "C" & column _
       & ":R" & lastRow & "C" & column 

这篇关于Excel 相关下拉列表(带/不带 VBA)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！