VBA运行时错误'70':无法设置列表属性.没有权限 [英] VBA Run time Error '70': Could not Set the list property. Permission Denied

问题描述

我正在尝试通过单击列表框来更新数据，但出现错误，请在下面查看我的代码.

I am trying to update the data by clicking the Listbox,but getting error, please see my code below.

使用以下代码在文本框中显示列表框的值

Using below code to display the list box value to text boxes

Private Sub ListBox1_Click()
Dim i As Integer
i = Me.ListBox1.ListIndex
Me.ListBox1.Selected(i) = True
Me.TextBox1.Value = Me.ListBox1.Column(0, i)
Me.TextBox2.Value = Me.ListBox1.Column(1, i)
Me.TextBox3.Value = Me.ListBox1.Column(2, i)
Me.TextBox4.Value = Me.ListBox1.Column(3, i)
End Sub

尝试使用以下代码更新数据并出现错误.

Trying to update data with following code and getting error.

Private Sub btnUpdate_Click()
If ListBox1.ListIndex <> -1 Then
With ListBox1
.List(.ListIndex, 0) = TextBox1.Value
.List(.ListIndex, 1) = TextBox2.Value
.List(.ListIndex, 2) = TextBox3.Value
.List(.ListIndex, 3) = TextBox4.Value
End With
End If
End Sub

enter code here

任何帮助将不胜感激.

推荐答案

与ComboBox不同，您不能像这样快速编辑列表框中的值.您必须删除该条目，然后使用新值将其插入其中.我不确定这是否是最优雅的方法，但是可以完成以下工作:

Unlike ComboBoxes, you can't edit values in a ListBox on the fly like this. You have to delete the entry and insert one back in, with your new values. I'm not sure this is the most elegant way to do it, but the following works:

Private Sub btnUpdate_Click()
    Dim values(3)
    Dim u As Long
    u = ListBox1.ListIndex
    If u <> -1 Then
            values(0) = TextBox1.Value
            values(1) = TextBox2.Value
            values(2) = TextBox3.Value
            values(3) = TextBox4.Value

         With ListBox1
         .RemoveItem u
         .AddItem values(0), u
            .List(u, 1) = values(1)
            .List(u, 2) = values(2)
            .List(u, 3) = values(3)
        End With
    End If
End Sub

Private Sub ListBox1_Click()
    Dim i As Long
    With Me
        i = .ListBox1.ListIndex
        .ListBox1.Selected(i) = True
        .TextBox1.Value = .ListBox1.Column(0, i)
        .TextBox2.Value = .ListBox1.Column(1, i)
        .TextBox3.Value = .ListBox1.Column(2, i)
        .TextBox4.Value = .ListBox1.Column(3, i)
    End With
End Sub

就像我说的那样，不是很优雅:我必须将TextBox1等的values存储在数组中，因为RemoveItem运行时，索引会发生变化，从而导致ListBox1_Click运行-重置所有文本框.

As I say, not elegant: I had to store the values of TextBox1 etc. in the array as when the RemoveItem runs, the Index changes which causes the ListBox1_Click to run - resetting all the Textboxes.

这篇关于VBA运行时错误'70':无法设置列表属性.没有权限的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！