依赖于Excel的下拉列表（带/不带VBA）

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.

虽然我对常规VB非常熟悉,但我对VBA完全不熟悉。我不知道这是否必须通过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.

我无法向您提供隐藏工作表上的数据列的示例,因为我不知道如何安排它们开始。

2 个解决方案

#1

Here you find a complete tutorial how to do this:

在这里,您可以找到完整的教程如何执行此操作:

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:

在您知道数据所在的区域(firstRow,lastRow,column)时,更改区域是VBA中的单行:

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

#2

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

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) _
             & "$2:$" & Chr(68 + col) & "$10" '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.

这应该为您提供一些东西。根据需要调整单元格地址,引用隐藏的工作表,不同数量的员工,部门,项目等。

#1