VBA用户窗体切换Combobox值时出现RunTime Error 70错误求助

• 核心问题：ComboBox控件的RowSource和List属性互斥。当你选中"ALL"选项时，代码给Me.CBC设置了RowSource = "BNme"，此时控件和工作表命名区域建立了绑定关系，运行时不允许直接修改List属性，因此同一会话切换到非"ALL"选项时，赋值Me.CBC.List = arrBDat就会触发错误70（权限被拒绝）。
• 隐藏问题：代码中cnt计数器没有在每次事件触发时重置为0，多次运行后会出现计数累加，导致数组内容错误、下标越界等异常。

你只需要按照以下两点修改代码即可：

1. 所有修改CBC数据源的操作前，先清空RowSource属性解除绑定
2. 每次进入事件逻辑时初始化cnt为0，避免计数累加

修复后完整代码：

Dim arrCDat As Variant
Dim arrBDat As Variant
Dim mysearch As String
Dim cnt As Long
Dim idx As Long
Dim Searchrange As Range

Application.DisplayAlerts = False
' 初始化计数器，避免累加
cnt = 0
' 先清空CBC的RowSource绑定，解除互斥限制
Me.CBC.RowSource = ""

Me.TBDS = Application.WorksheetFunction.VLookup(Me.CBRS.Value, Sheet2.Range("MLookup"), 2, 0)
Me.TBCC = Application.WorksheetFunction.VLookup(Me.TBDS.Value, Sheet2.Range("CCLookup"), 2, 0)
Me.TBR = Application.WorksheetFunction.VLookup(Me.CBRS.Value, Sheet2.Range("A:C"), 3, 0)
    
If Me.TBR = "ALL" Then
    Me.CBC.RowSource = "BNme"
Else
    If Me.TBR = "MT" Then
        Me.CBC.RowSource = "MT"
    Else
        If Me.TBR.ListIndex <> -1 Then
            mysearch = Me.TBR.Value
    
            With Sheets("Lookups")
                Set Searchrange = .Range("T2", .Range("T" & .Rows.Count).End(xlUp))
            End With
    
            arrCDat = Searchrange.Resize(, 3).Value
    
            ReDim arrBDat(1 To UBound(arrCDat, 1))
        
            For idx = LBound(arrCDat, 1) To UBound(arrCDat, 1)
                If arrCDat(idx, 1) = mysearch Then
                    Me.CBC = arrCDat(idx, 2)
                    cnt = cnt + 1
                    arrBDat(cnt) = arrCDat(idx, 2)
                End If
            Next idx
        
            If cnt > 0 Then
                ReDim Preserve arrBDat(1 To cnt)
                ' 一维数组用Column属性赋值更适配，避免List属性的维度不兼容问题
                Me.CBC.Column = arrBDat
            Else
                MsgBox "Uh oh, things have gone a little sideways and the LO " & mysearch & " cannot be located. Please try another Option!"
            End If
        Else
            Exit Sub
        End If
    End If
End If
Application.DisplayAlerts = True

补充说明：原代码遗漏了最后恢复Application.DisplayAlerts = True的逻辑，不恢复会导致后续Excel操作的提示弹窗被全部屏蔽，建议按上述代码补充。

内容的提问来源于stack exchange，提问作者MBrann