Geek asks and answers

Excel dependent dropdown lists (with / without VBA)

I am completely new to VBA, although I am very familiar with regular VB. I don’t know if it needs to be done through VBA, or if it can be done with Excel built-in functions. Basically, I have a bunch of data in columns on a hidden sheet. The other sheet should have two drop-down lists, one of which depends on the other selected value. There are three objects in a relationship (see the example below). I don’t know how to do this, or even how the relationships between these lines will work.

Here is an example of what I want to achieve: On one sheet there are two columns: “Employee” and “Project”. The employee contains a drop-down list of employees. This data is in a hidden sheet. Each employee is associated with one department, and each department is associated with a number of projects. When the user has selected a specific employee, I want the Active column in the Project column to populate a drop-down list containing all the projects related to the department to which the selected employee belongs. How can I establish this relationship in Excel and write a VBA function that makes this function?

I can’t give you an example of what the data columns look like on a hidden sheet, since I have no idea how to organize them.

+2
source share
2 answers

, :

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

VBA , , . , - "" ( VBA). , , .

VBA, (firstRow, lastRow, column), :

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

:

(A, B, C) 1 9. . , , , .

:

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

A7 , , :

  • → → → = $A $2: $A $4

:

Private Sub Worksheet_Change(ByVal Target As Range)
    Call FillDropDown
End Sub

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

- . , , , , ..

+6

More articles:


All Articles