修复可搜索ComboBox时出现运行时错误'70'：权限被拒绝

我开发了一个可搜索的ComboBox控件，运行时出现错误'70'：权限被拒绝，错误截图如下：

相关代码：

Private Sub cmb_phanloaitheomucdo_12_Change()
    Dim i As Long
    If Not Comb_Arrow Then
        With Me.cmb_phanloaitheomucdo_12
            .List = Worksheets("Sheet1").Range(Sheet1.Range("B2"), Worksheets("Sheet1").Cells(Rows.Count, "B").End(xlUp)).Value
            .ListRows = Application.WorksheetFunction.Min(4, .ListCount)
            .DropDown
            If Len(.text) Then
                For i = .ListCount - 1 To 0 Step -1
                    If InStr(1, .List(i), .text, vbTextCompare) = 0 Then .RemoveItem i
                Next
                .DropDown
            End If
        End With
    End If
End Sub

Private Sub cmb_phanloaitheomucdo_12_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = vbKeyReturn Then Me.txt_phanloaieau_13.SetFocus
End Sub

排查与解决要点 #

• 事件递归触发导致控件锁定：Change事件会在ComboBox内容修改时自动触发，你在事件内又修改控件的List和移除项，会反复触发事件，导致控件资源被锁定，引发权限拒绝错误。
  解决：操作控件前禁用事件，操作完成后恢复，同时加错误处理确保事件不会一直禁用。

• 工作表引用不一致：代码中同时使用Worksheets("Sheet1")和Sheet1，如果工作表的名称和CodeName不匹配，可能引发隐性的权限或引用错误。
  解决：统一使用一种引用方式，比如直接用Sheet1（工作表的CodeName，在VBE的属性窗口中修改）。

• 控件操作顺序不合理：设置List后立即修改ListRows并调用DropDown，控件可能未完成列表加载就被再次操作，导致冲突。

修改后的代码示例 #

Private Sub cmb_phanloaitheomucdo_12_Change()
    Dim i As Long
    Dim rngSource As Range
    
    If Not Comb_Arrow Then
        ' 禁用事件，避免递归触发
        Application.EnableEvents = False
        On Error GoTo Cleanup ' 确保出错时也能恢复事件
        
        With Me.cmb_phanloaitheomucdo_12
            ' 统一引用工作表数据源
            Set rngSource = Sheet1.Range(Sheet1.Range("B2"), Sheet1.Cells(Rows.Count, "B").End(xlUp))
            .List = rngSource.Value
            
            ' 先判断列表数量再设置显示行数
            If .ListCount > 0 Then
                .ListRows = Application.WorksheetFunction.Min(4, .ListCount)
            End If
            
            If Len(.Text) > 0 Then
                ' 移除不匹配的列表项
                For i = .ListCount - 1 To 0 Step -1
                    If InStr(1, .List(i), .Text, vbTextCompare) = 0 Then
                        .RemoveItem i
                    End If
                Next i
                .DropDown
            End If
        End With
        
Cleanup:
        ' 恢复事件触发
        Application.EnableEvents = True
        ' 错误提示（可选）
        If Err.Number <> 0 Then
            MsgBox "错误信息：" & Err.Description & vbCrLf & "错误代码：" & Err.Number
        End If
    End If
End Sub

Private Sub cmb_phanloaitheomucdo_12_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = vbKeyReturn Then Me.txt_phanloaieau_13.SetFocus
End Sub

额外检查点 #

确认Comb_Arrow变量已正确定义（比如在模块顶部声明为Public或Private），且在ComboBox的DropButtonClick事件中正确赋值为True，避免非下拉操作时错误执行代码逻辑。

内容的提问来源于stack exchange，提问作者Bảo Ngọc Dương