Excel实战技巧68：创建级联列表框（使用ADO技巧）
- 2019 年 12 月 24 日
- 筆記
在《Excel实战技巧67：在组合框中添加不重复值（使用ADO技巧）》中，我们使用记录集技巧给组合框添加了不重复值，并概要讲述了ADO记录集基础知识。本文利用记录集技巧，创建级联列表框。
示例效果如下图1所示。
图1
正如上图1所演示的，创建的一组列表框－Region，Market和State可以联动工作。也就是说，如果选择列表框Region中的某项，那么列表框Market和State仅显示在所选择的Region项中与该项关联的值。同样，选择列表框Market中的某项，列表框State中仅显示与Market项中与该项关联的值。
解决方法
使用ADO记录集为子列表框提取记录，使用父列表框的值作为条件。在这种情况下，Region和Markets都是父列表框，因为它们影响如何提供下一级的值。Market和State作为子列表框，因为它们的值取决于其上一级列表框。
在本示例中，创建一个函数，接受子列表框作为其参数，然后使用该列表框判断提取什么数据以及填充哪个列表框。
打开VBE，插入一个标准模块，输入下列代码：
Function CascadeChild(TargetChild As OLEObject) Dim Myconnection As Connection Dim Myrecordset As Recordset Dim Myworkbook As String Dim strSQL As String Set Myconnection = NewConnection Set Myrecordset = NewRecordset '识别要引用的工作簿 Myworkbook =Application.ThisWorkbook.FullName '打开对该工作簿的连接 Myconnection.Open"Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source="& Myworkbook & ";" & _ "ExtendedProperties=Excel 8.0;" & _ "Persist SecurityInfo=False" '确定正确的SQL语句，在父列表框中使用该值作为查询的参数 Select CaseTargetChild.Name Case Is ="lstMarket" strSQL = "SelectDistinct [Market] AS [tgtField] from [Sheet1$A1:C40] Where [Region]='"& Sheet1.lstRegion.Value & "'" Case Is ="lstState" strSQL = "SelectDistinct [State] AS [tgtField] from [Sheet1$A1:C40] Where [Market]='"& Sheet1.lstMarket.Value & "'" End Select '装载查询到记录集中 Myrecordset.Open strSQL,Myconnection, adOpenStatic '填充目标子列表框 With TargetChild.Object .Clear Do .AddItemMyrecordset![tgtField] Myrecordset.MoveNext Loop UntilMyrecordset.EOF '自动选择列表框中的第一个值 .Value = .List(0) End With '清理 Myconnection.Close Set Myrecordset = Nothing Set Myconnection =Nothing End Function
每个父列表框的OnClick事件只是简单地调用上面的函数，传递目标子列表框作为函数的参数：
Private Sub lstMarket_Click() CallCascadeChild(ActiveSheet.OLEObjects(Sheet1.lstState.Name)) End Sub Private Sub lstRegion_Click() Call CascadeChild(ActiveSheet.OLEObjects(Sheet1.lstMarket.Name)) End Sub
说明
1.示例中使用的是ActiveX列表框控件。
2.需要在VBE中设置对Microsoft ActiveX Data Objects Library的引用，如下图2所示。
图2
3.可以使用如下所示的命名区域代替硬编码单元格区域：
Myrecordset.Open “Select Distinct [Market] from [命名区域]”
