Excel dependent dropdown lists (With/without 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.

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.

回答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.

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 

回答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.

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:

• 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:

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.