前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >Excel实战技巧68:创建级联列表框(使用ADO技巧)

Excel实战技巧68:创建级联列表框(使用ADO技巧)

作者头像
fanjy
发布2019-12-24 16:30:58
1.3K0
发布2019-12-24 16:30:58
举报
文章被收录于专栏:完美Excel

在《Excel实战技巧67:在组合框中添加不重复值(使用ADO技巧)》中,我们使用记录集技巧给组合框添加了不重复值,并概要讲述了ADO记录集基础知识。本文利用记录集技巧,创建级联列表框。

示例效果如下图1所示。

图1

正如上图1所演示的,创建的一组列表框-Region,Market和State可以联动工作。也就是说,如果选择列表框Region中的某项,那么列表框Market和State仅显示在所选择的Region项中与该项关联的值。同样,选择列表框Market中的某项,列表框State中仅显示与Market项中与该项关联的值。

解决方法

使用ADO记录集为子列表框提取记录,使用父列表框的值作为条件。在这种情况下,Region和Markets都是父列表框,因为它们影响如何提供下一级的值。Market和State作为子列表框,因为它们的值取决于其上一级列表框。

在本示例中,创建一个函数,接受子列表框作为其参数,然后使用该列表框判断提取什么数据以及填充哪个列表框。

打开VBE,插入一个标准模块,输入下列代码:

代码语言:javascript
复制
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事件只是简单地调用上面的函数,传递目标子列表框作为函数的参数:

代码语言:javascript
复制
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 [命名区域]”

本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2019-12-15,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 完美Excel 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档