Excel VBA給Listbox 或ComboBox 設(shè)置 時出現(xiàn)權(quán)限拒絕70的錯誤解決辦法

Excel VBA給Listbox 或ComboBox 設(shè)置 時出現(xiàn)權(quán)限拒絕的錯誤解決辦法

英文的錯誤 提示： Run time error 70: Permission denied

這種情況多數(shù)是在Listbox的屬性設(shè)置中設(shè)置了rowsource  屬性 或VBA代碼中設(shè)置了rowsource 屬性

然后又使用了Additem 或 設(shè)置.list 來設(shè)置屬性，就會導(dǎo)致這種錯誤提示

解決辦法 ：

將屬性設(shè)置中的 rowsource 屬性 設(shè)置為空即可

如下面的代碼 也是因為屬性設(shè)置了Recordsource ,所以在下面這句會出現(xiàn) 權(quán)限拒絕的錯誤

Userform1.IbEmp.AddItem rngEmp(i)

The full code is:
Sub Show_Countries()
Dim rngEmp() As String
Dim emps As String
Dim i As Integer

Application.Sheets("Sheet1").Select
emps = ActiveCell.Offset(0, -1).Value
If emps = "" Then
MsgBox ("No Name(s) Available")
End
Else
rngEmp = Split(emps, ",")
End If

I also have codes for buttons in the combo box:


Private Sub closewindow_Click()
Unload Userform1
End Sub

Private Sub reset_Click()
Userform1.IbEmp.Clear
End Sub
Private Sub submit_Click()
Dim emps As String
emps = ""
For i = 0 To Userform1.IbEmp.ListCount - 1
If IbEmp.Selected(i) = True Then
'MsgBox IbEmp.List(i)
If emps = "" Then
emps = IbEmp.List(i)
ElseIf emps <> "" Then
emps = emps & "," & IbEmp.List(i)
End If
End If
Next i
ActiveCell.Value = emps
End Sub


For i = LBound(rngEmp) To UBound(rngEmp)
Userform1.IbEmp.AddItem rngEmp(i)
Next

Userform1.Show
End Sub

以下是微軟官網(wǎng)上一個網(wǎng)友遇到的，也是類似的問題 引起的