Excel VBA給Listbox 或ComboBox 設置 時出現權限拒絕70的錯誤解決辦法

2019-11-13 08:35:00
zstmtony
原創
98

Excel VBA給Listbox 或ComboBox 設置 時出現權限拒絕的錯誤解決辦法

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


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

然后又使用了Additem 或 設置.list 來設置屬性,就會導致這種錯誤提示

解決辦法 :


將屬性設置中的 rowsource 屬性 設置為空即可



如下面的代碼 也是因為屬性設置了Recordsource ,所以在下面這句會出現 權限拒絕的錯誤



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


以下是微軟官網上一個網友遇到的,也是類似的問題 引起的


lb_ExtContacts.List = ar_ListBoxContacts

results in Run-time error 70, permission denied.

What I really want is to change the listbox value based on the click event and to capture the new value in the array, which I'll use later to update cells in a worksheet range.  If there's a more efficient way to do this that doesn't involve refreshing the whole listbox when I change a single item that would be ideal.  But the main thing is I need something that works!  
Here's the code:

Private Sub lb_ExtContacts_Click()
'This routine toggles rows in the listbox as follows:
' If the row status is blank, clicking it changes to
' Active.  If the status is active, clicking changes
' it to Inactive.  If the status is Inactive, clicking
' changes it to Active.  You can't "delete" a row
' using this dialog box.

  With lb_ExtContacts
  
    int_CurrentValue = .ListIndex
  
    Select Case ar_ListBoxContacts(.ListIndex + 1, 4)
      Case Is = "Active"
        ar_ListBoxContacts(.ListIndex + 1, 4) = "Inactive"
      Case Is = "Inactive"
        ar_ListBoxContacts(.ListIndex + 1, 4) = "Active"    
      Case Is = ""
        ar_ListBoxContacts(.ListIndex + 1, 4) = "Active"
    End Select
  End With
  
  lb_ExtContacts.List = ar_ListBoxContacts
  lb_ExtContacts.ListIndex = int_CurrentValue
  lb_ExtContacts.SetFocus

End Sub


分享
北京十一选五基本走势