VBA--用类实现组合框联动

1.首先，我们要创建一个类，即添加类模板，记得更改名字，并且在里面添加相应的字段，并且对其字段和对象进行属性约束，其具体代码如下：

Option Explicit
Private sprovince As String
Private scity As Object
'属性：省
Property Let province(pro As String)
    sprovince = pro
End Property
Property Get province() As String
    province = sprovince
End Property
'属性：市
Property Set city(ci As Object)
    Set scity = ci
End Property
Property Get city() As Object
    Set city = scity
End Property

Private Sub Class_Initialize()
     Set city = CreateObject("scripting.dictionary") '市为一个字典，存储数据
    
End Sub

Private Sub Class_Terminate()
    Set city = Nothing '释放空间
   
End Sub

2.在Excel表内导入数据，具体数据如下图所示

3.创建一个窗体，布局和样式如下图所示

4.编写窗体的触发时间，及运行事件

Option Explicit
Dim arrdata() As clsdata '创建一个对象（clsdata)
Dim d As Object '定义一个字典

Private Sub probox_Change()
With citbox
    .Clear '清理文本
    .List = arrdata(d(probox.Text)).city.Keys '根据对应的省份获取市的信息
    .ListIndex = 0 '使复合框中默认显示第一个数据
End With
End Sub

Private Sub UserForm_Initialize()
    Dim i As Integer, dic As Object
    Set dic = CreateObject("scripting.dictionary") '创建一个字典来存储省的数据
    For i = 2 To 309
        dic(Cells(i, 1).Value) = ""  '将省份信息存入dic
    Next i
    ReDim arrdata(dic.Count - 1) As clsdata '确定数组的长度（有多少个省，长度就为多少）
    Set d = CreateObject("scripting.dictionary") 'city 字典
    For i = 0 To dic.Count - 1
        Set arrdata(i) = New clsdata '每增加一个省份，自动添加一个字典
        arrdata(i).province = dic.Keys()(i) '数组的省份信息等于dic字典的值
        d(arrdata(i).province) = i '每个省份对应一个编号
    Next i
    For i = 2 To 309
        arrdata(d(Cells(i, 1).Value)).city(Cells(i, 2).Value) = "" '根据省份来获取对应的市
       
    Next i
    probox.List = dic.Keys '所有的省份
   
End Sub