VBA: 采用Combox控件实现二级下拉菜单功能

Option Explicit

Private Sub provinceselect_Change()
    
    'Populate city data
    
    Dim i As Integer, j As Integer, nrow As Integer
    
    Sheets("Sheet1").Select
    
    'Clear old data, if exists
    UserForm1.cityselect.Clear
    
    nrow = Range("A65535").End(xlUp).Row
    
    For i = 1 To nrow
    
        j = 1
        
        If Range("A1:A" & nrow).Cells(i, 1) = UserForm1.provinceselect.Text Then
        
            Range("A" & i).Select
        
            Do While Not IsEmpty(ActiveCell.Offset(j, 0))
            
                UserForm1.cityselect.AddItem ActiveCell.Offset(j, 0)
                
                j = j + 1
                
            Loop
        
            UserForm1.cityselect.Text = ActiveCell.Offset(1, 0)
        
            Exit For
            
        End If
        
    Next i
    
End Sub

Private Sub UserForm_Initialize()

    'Populate Provinces
    
    Dim ncategories As Integer, i As Integer
    
    Sheets("Sheet1").Select
    
    ncategories = WorksheetFunction.CountA(Columns("C:C"))
    
    For i = 1 To ncategories
    
        UserForm1.provinceselect.AddItem Range("C1:C" & ncategories).Cells(i, 1)
        
    Next i
    
    UserForm1.provinceselect.Text = Range("C1").Value
    
End Sub