首页 > Excel专区 > Excel教程 >

excel图表制作:创建级联列表框

Excel教程 2022-01-10 19:04:53

本文利用excel图表记录集技巧,创建级联列表框。

示例效果如下图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


标签: Excel图表制作Excel常用函数excel数据透视表excel教程

office教程网 Copyright © 2016-2020 https://www.office9.cn. Some Rights Reserved.