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 [命名區域]」
