根据其他选择列表动态更新选择列表 [英] Dynamically updating select list based on another select list

问题描述

我想根据用户在另一个中的选择填写一个下拉列表。在线找不到任何相关的内容。

I want fill a drop down list based on user selection in another. Can't find anything related to it online.

我有一个名为 lookupDept 的工作簿，其中包含以下表格：

I have a workbook called lookupDept containing the following table:

A   B
==  ==============================
BS  Business School
CG  Chemical Engineering

其中列A 具有定义名称 deptCode 和列B 具有定义的名称 deptName 。我有一个名为 lookupModule 的第二个工作簿，其中包含下表：

where column A has the defined name deptCode and column B has the defined name deptName. I have a second workbook called lookupModule which contains the following table:

A       B                                                   C
======  ==================================================  ==
BSA005  Organisational Behaviour                            BS
BSA007  Skills for Study                                    BS
CGA001  Fluid Mechanics I MEng & BEng Status                CG
CGA002  Stagewise Processes                                 CG

我正在尝试更新 cbo_moduleCode 在我的表单上选择 lookupDept 中的列A 匹配 lookupModule 中的列C 的范围。这是我使用的代码：

I'm trying to update cbo_moduleCode on my form to select a range where column A in lookupDept matches column C in lookupModule. This is the code I'm using:

所以如果用户选择表单上的 BS - 商学院 strong> lookupDept 工作簿，我希望选择 lookupModule 工作簿的C列中包含 BS 的所有字段，这是我使用的代码：

So if the user picks BS - Business School on the form (which is extracted from the lookupDept workbook, I want all the fields containing BS in column C of the lookupModule workbook to selected. This is the code I'm using so far:

Private Sub UserForm_Initialize()

    Dim c_deptCode As Range
    Dim c_deptName As Range

    Dim deptCodes As Variant
    Dim deptNames As Variant

    Dim ws_dept As Worksheet
    Dim ws_misc As Worksheet
    Set ws_dept = Worksheets("lookupDept")
    Set ws_misc = Worksheets("lookupMisc")

    ' Assign each range to an array containing the values
    deptCodes = Choose(1, ws_dept.Range("deptCode"))
    deptNames = Choose(1, ws_dept.Range("deptName"))

    ' Create deptcode+deptname cbo
    For i = 1 To ws_dept.Range("deptCode").Rows.Count
        CombinedName = deptCodes(i, 1) & " - " & deptNames(i, 1)
        cbo_deptCode.AddItem CombinedName
    Next i

End Sub

推荐答案

选择第一个组合框后，您可以随时添加项目。此代码是一个示例，可能会导致您的方向正确：

You can try adding the items on the fly once the first combobox is selected. This code is a sample that might lead you in the right direction:

Private Sub cbo_deptCode_Change()
Dim lLoop As Long, rgLoop As range

For lLoop = 1 To Me.cbo_moduleCode.ListCount

    Me.cbo_moduleCode.RemoveItem 0

Next lLoop

Sheets("lookupModule").[a1].CurrentRegion.AutoFilter
Sheets("lookupModule").[a1].CurrentRegion.AutoFilter Field:=3, Criteria1:=Left(Me.cbo_deptCode.Value, 2)

For Each rgLoop In Sheets("lookupModule").[a1].CurrentRegion.Offset(1).SpecialCells(xlCellTypeVisible).Columns(1).Cells
    If Len(rgLoop) > 0 Then
        Me.cbo_moduleCode.AddItem rgLoop & " - " & rgLoop.Offset(, 1)
    End If
Next rgLoop

End Sub

这篇关于根据其他选择列表动态更新选择列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！