Run time error 70…..Could not set the list property . permission denied!

Topic

New Reply

I don’t know why this happening but i get it when i try to update a list box value.my intention was to use two textboxes to get listbox values of colum 0 and 1 after double click then change values in textboxes and click update “commandbutton1” to change the value. ultimately i want to send the changes to the spreadsheet to update the table where the listbox is getting its values from.

Thank you so much in advance for any help you can provide. i spend hours trying things but nothing want to work.
here is my code:

Code:

Private Sub CommandButton1_Click()
‘code to update listbox after cahnging values in textboxes


 If ListBox1.ListIndex  -1 Then
        With ListBox1
            .List(.ListIndex, 0) = txtDepartment.Value
            .List(.ListIndex, 1) = txtType.Value
           
        End With
    End If
End Sub




Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
‘code to get listbox column o and 1 to textboxes in the same form
Label3.Visible = True
Label4.Visible = True
txtDepartment.Visible = True
txtType.Visible = True
UserForm3.txtDepartment.Text = ListBox1.List(ListBox1.ListIndex)
UserForm3.txtType.Text = ListBox1.List(ListBox1.ListIndex, 1)


End Sub


Private Sub UserForm_Initialize()




  ‘Puts the user form in the middle of the Excel screen when opened.
    Me.StartupPosition = 0
    Me.Top = (Application.Height / 2) – (Me.Height / 2)
    Me.Left = (Application.Width / 2) – (Me.Width / 2)


‘populate list boxes with dynamic ranges
ListBox1.RowSource = “GLDepartment”
ListBox2.RowSource = “ExpenseType”
txtDepartment.Visible = False
txtType.Visible = False
Label3.Visible = False
Label4.Visible = False


End Sub

Reply | Quote

Replies

abouya,

The problem you are experiencing is that you are attempting to directly change the list values in the listbox without changing the values in the named range that they are bound to. The approach you need to take is to change the values in the named range and then the listbox list values will follow suite. To have your textboxes change the values in the named range at the location specified by your double clicked listbbox selection (listIndex), you can use something like this.

Code:

Private Sub CommandButton1_Click()
‘code to update listbox after cahnging values in textboxes
If ListBox1.ListIndex  -1 Then
    With ListBox1
        Range(“GLDepartment”)((.ListIndex * 2) + 1) = txtDepartment.Value
        Range(“GLDepartment”)((.ListIndex * 2) + 2) = txtType.Value
    End With
End If
End Sub

The code assumes you are using multicolumn listboxes with 2 columns each and your named ranges have two columns. I have shown only listbox1 in the example but repeat it for listbox2.

HTH,
Maud

Reply | Quote

abouya,

The problem you are experiencing is that you are attempting to directly change the list values in the listbox without changing the values in the named range that they are bound to. The approach you need to take is to change the values in the named range and then the listbox list values will follow suite. To have your textboxes change the values in the named range at the location specified by your double clicked listbbox selection (listIndex), you can use something like this.

Code:

Private Sub CommandButton1_Click()
‘code to update listbox after cahnging values in textboxes
If ListBox1.ListIndex  -1 Then
    With ListBox1
        Range(“GLDepartment”)((.ListIndex * 2) + 1) = txtDepartment.Value
        Range(“GLDepartment”)((.ListIndex * 2) + 2) = txtType.Value
    End With
End If
End Sub

The code assumes you are using multicolumn listboxes with 2 columns each and your named ranges have two columns. I have shown only listbox1 in the example but repeat it for listbox2.

HTH,
Maud

Hello Maud,

Thank you so much again, it does work now. i do find it difficult to delete selected item, I added a command button to delete selected row but it deletes entire row, how do i restrict it for column A and be only since listbox 1 has two columns? code i’m using to delete selected row is in Bold

here is the code i’m using to delete selected listbox items: “if you notice, the code underlined is to make sure user won’t delete last item left in listbox1 because i noticed it mess with the dynamic range i set up for listbox1.

Code:

Private Sub CommandButton3_Click()
 Dim i As Long
  
  If txtDepartment.Visible = False And txtType.Visible = False Then
MsgBox “Please double click the Item you want to remove first in the above list!”
Exit Sub
Else
    i = ListBox1.ListIndex + 1
[U]    If i = 1 Then[/U]
[U]    MsgBox “Can’t delete the last Item left in the list, Please Modify it instead!”[/U]
[U]    Exit Sub[/U]
    Else
[B]    With ThisWorkbook.Sheets(“GL”)    ‘need to delete row(i) but only columns A and B![/B]
[B]        .Rows.EntireRow(i + 1).Delete[/B]
[B]    End With[/B]
    txtDepartment.Visible = False
    txtType.Visible = False
    Label3.Visible = False
    Label4.Visible = False
    ListBox1.RowSource = “GLDepartment”
End If
End If
End Sub

Thanks for your help

Reply | Quote

abouya,

Remember, when your listbox is bound to a named range, all changes must be made to the named range not directly to the list box selections. So, if you want to delete values from column 1 of your multicolumn listbox, you will need to delete these values from column 1 of your named range. When deleting values from the column, the Shift:=xlUp prevents the named range from shrinking so you do not need to test if it is the last value.

HTH,
Maud

Code:

Private Sub CommandButton3_Click()
Dim i As Long
If txtDepartment.Visible = False And txtType.Visible = False Then
    MsgBox “Please double click the Item you want to remove first in the above list!”
    Exit Sub
Else:
    [COLOR=”#0000FF”]With ListBox1
        Range(“GLDepartment”)((.ListIndex * 2) + 1).Delete Shift:=xlUp
        [COLOR=”#008000″]’Range(“GLDepartment”)((.ListIndex * 2) + 2).Delete Shift:=xlUp  IF YOU WANT TO DELETE FROM COL 2[/COLOR]
    End With[/COLOR]
    txtDepartment.Visible = False
    txtType.Visible = False
    Label3.Visible = False
    Label4.Visible = False
End If
End Sub

Reply | Quote

abouya,

Remember, when your listbox is bound to a named range, all changes must be made to the named range not directly to the list box selections. So, if you want to delete values from column 1 of your multicolumn listbox, you will need to delete these values from column 1 of your named range. When deleting values from the column, the Shift:=xlUp prevents the named range from shrinking so you do not need to test if it is the last value.

HTH,
Maud

Code:

Private Sub CommandButton3_Click()
Dim i As Long
If txtDepartment.Visible = False And txtType.Visible = False Then
    MsgBox “Please double click the Item you want to remove first in the above list!”
    Exit Sub
Else:
    [COLOR=#0000FF]With ListBox1
        Range(“GLDepartment”)((.ListIndex * 2) + 1).Delete Shift:=xlUp
        [COLOR=#008000]’Range(“GLDepartment”)((.ListIndex * 2) + 2).Delete Shift:=xlUp  IF YOU WANT TO DELETE FROM COL 2[/COLOR]
    End With[/COLOR]
    txtDepartment.Visible = False
    txtType.Visible = False
    Label3.Visible = False
    Label4.Visible = False
End If
End Sub

Hi Maud,

Range(“GLDepartment”)((.ListIndex * 2) + 2). Delete Shift:=xlUp gives me run time error 1004 “Application defined or object defined error”!
maybe because that above code doesn’t include the “i” that i set up to check so that user won’t delete table first value so range won’t get lost!

Reply | Quote

Abouya,

Strange, it works fine for me. Back in excel, if you select the name range “GLDepartment”, is it still valid? What is the range selected?

Here is how it all works:
In a 2 column named range the cells are indexed horizontally then move to the next row. In the below example, here is a two column named range named “Abouya” from A3:B7. The numbers in the cell refers to the index.

46734-abouya1

Range(“Abouya”)(1) refers to cell A3
Range(“Abouya”)(2) refers to cell B3
Range(“Abouya”)(3) refers to cell A4
Range(“Abouya”)(4) refers to cell B4
Range(“Abouya”)(5) refers to cell A5
and so on….

So, when deleting a cell in the range using the listIndex you need a formula to find the correct cell

Column 1
If ListIndex=0 then (.ListIndex * 2)+1 =1. Therefore, Range(“Abouya”)((.ListIndex * 2) + 1) refers to cell A3
If ListIndex=1 then (.ListIndex * 2)+1 =3. Therefore, Range(“Abouya”)((.ListIndex * 2) + 1) refers to cell A4
If ListIndex=2 then (.ListIndex * 2)+1 =5. Therefore, Range(“Abouya”)((.ListIndex * 2) + 1) refers to cell A5
and so on….

Column 2
If ListIndex=0 then (.ListIndex * 2)+2 =2. Therefore, Range(“Abouya”)((.ListIndex * 2) + 2) refers to cell B3
If ListIndex=1 then (.ListIndex * 2)+2 =4. Therefore, Range(“Abouya”)((.ListIndex * 2) + 2) refers to cell B4
If ListIndex=2 then (.ListIndex * 2)+2 =6. Therefore, Range(“Abouya”)((.ListIndex * 2) + 2) refers to cell B5

The code line: Range(“GLDepartment”)((.ListIndex * 2) + 1).Delete Shift:=xlUp
will take the value of the ListIndex (which is the selected row starting with row 0), covert it to the cell index in the named range, delete the referred cell then shift all the cells in that column up one. The size of the name range does not change because the cells have shifted up to replace it. In your listbox in column 1, you will notice that the last row col 1 is blank. You can remove all the values in column 1 and the named range will still be the same size however, column 1 in the listbox will all be blank. Therefore, you do not need the “i” loop to test that a row is present. The size of the named range will not change.

Please make sure your named range “GLDepartment” defines two columns. If you are still having issues, please post a sample and I will attempt to correct the source of the problem.

HTH,
Maud

Reply | Quote

Abouya,

Strange, it works fine for me. Back in excel, if you select the name range “GLDepartment”, is it still valid? What is the range selected?

Here is how it all works:
In a 2 column named range the cells are indexed horizontally then move to the next row. In the below example, here is a two column named range named “Abouya” from A3:B7. The numbers in the cell refers to the index.

46734-abouya1

Range(“Abouya”)(1) refers to cell A3
Range(“Abouya”)(2) refers to cell B3
Range(“Abouya”)(3) refers to cell A4
Range(“Abouya”)(4) refers to cell B4
Range(“Abouya”)(5) refers to cell A5
and so on….

So, when deleting a cell in the range using the listIndex you need a formula to find the correct cell

Column 1
If ListIndex=0 then (.ListIndex * 2)+1 =1. Therefore, Range(“Abouya”)((.ListIndex * 2) + 1) refers to cell A3
If ListIndex=1 then (.ListIndex * 2)+1 =3. Therefore, Range(“Abouya”)((.ListIndex * 2) + 1) refers to cell A4
If ListIndex=2 then (.ListIndex * 2)+1 =5. Therefore, Range(“Abouya”)((.ListIndex * 2) + 1) refers to cell A5
and so on….

Column 2
If ListIndex=0 then (.ListIndex * 2)+2 =2. Therefore, Range(“Abouya”)((.ListIndex * 2) + 2) refers to cell B3
If ListIndex=1 then (.ListIndex * 2)+2 =4. Therefore, Range(“Abouya”)((.ListIndex * 2) + 2) refers to cell B4
If ListIndex=2 then (.ListIndex * 2)+2 =6. Therefore, Range(“Abouya”)((.ListIndex * 2) + 2) refers to cell B5

The code line: Range(“GLDepartment”)((.ListIndex * 2) + 1).Delete Shift:=xlUp
will take the value of the ListIndex (which is the selected row starting with row 0), covert it to the cell index in the named range, delete the referred cell then shift all the cells in that column up one. The size of the name range does not change because the cells have shifted up to replace it. In your listbox in column 1, you will notice that the last row col 1 is blank. You can remove all the values in column 1 and the named range will still be the same size however, column 1 in the listbox will all be blank. Therefore, you do not need the “i” loop to test that a row is present. The size of the named range will not change.

Please make sure your named range “GLDepartment” defines two columns. If you are still having issues, please post a sample and I will attempt to correct the source of the problem.

HTH,
Maud

Hello Maud,
I tried many things to fix this issue but nothing seems to help. I attached a simple file for you hoping you can help me out with this problem. Just first column in range “GLDepartment” get deleted, the second column i receive a run time error like before. Another thing that happens is that the range “GLDepartment” losses its definition from named ranges when table is empty. the range is defined with an offset formula and Counta.

Thanks again.

Reply | Quote

Abouya,

Thanks for the sample. You had dynamic ranges which were different than I had anticipated. I now understand where you were coming from with your concern of the decrementing dynamic range as you removed rows. The needed modifications were simple changes and are highlighted in blue. Repeat the same steps for Listbox2.

Let me know if you need additional assistance.
Maud

Code:

Private Sub CommandButton3_Click()
If txtDepartment.Visible = False And txtType.Visible = False Then
    MsgBox “Please double click the Item you want to remove first in the above list!”
    Exit Sub
Else
    [COLOR=”#0000FF”]If ListBox1.ListIndex > 0 Then[/COLOR]  [COLOR=”#008000″]’PREVENT REMAINING ROW FROM BEING DELETED[/COLOR]
        With ListBox1
            [COLOR=”#0000FF”]Range(“GLDepartment”)((.ListIndex * 2) + 2).Delete  [/COLOR][COLOR=”#008000″]’REMOVE SHIFT:XLUP.  DELETE SECOND COLUMN BEFORE FIRST[/COLOR]
            [COLOR=”#0000FF”]Range(“GLDepartment”)((.ListIndex * 2) + 1).Delete  [/COLOR][COLOR=”#008000″]’REMOVE SHIFT:XLUP.  DELETE FIRST COLUMN AFTER SECOND[/COLOR]
        End With
        txtDepartment.Visible = False
        txtType.Visible = False
        Label3.Visible = False
        Label4.Visible = False
        CommandButton1.Visible = False
        CommandButton3.Visible = False
    End If
End If
End Sub

Reply | Quote

Maud,

It works like a charm now. I can’t thank you enough. we truly need more like you in the world.

I appreciate.

Reply | Quote

Hi Maud,

I’m getting v=crazy trying to find out what this method you showed me doesn’t work:

I’m trying to use textboxes1-8 to update a listbox4.

Code:

Private Sub CommandButton14_Click() ‘change button for Vendors


If ListBox4.ListIndex = -1 Then
MsgBox “Choose an item”, vbExclamation
Exit Sub
End If


If ListBox4.ListIndex  -1 Then
    With ListBox4
        
       
        Range(“vendors”)((.ListIndex * 8) + 1) = UserForm3.TextBox1.Value
        Range(“vendors”)((.ListIndex * 8) + 2) = UserForm3.TextBox2.Value
        Range(“Vendors”)((.ListIndex * 8) + 3) = UserForm3.TextBox3.Value
        Range(“Vendors”)((.ListIndex * 8) + 4) = UserForm3.TextBox4.Value
        Range(“Vendors”)((.ListIndex * 8) + 5) = UserForm3.TextBox5.Value
        Range(“Vendors”)((.ListIndex * 8) + 6) = UserForm3.TextBox6.Value
        Range(“Vendors”)((.ListIndex * 8) + 7) = UserForm3.TextBox7.Value
        Range(“Vendors”)((.ListIndex * 8) + 8) = UserForm3.TextBox8.Value


    End With


Call Main ‘Progress Bar
MsgBox “Item has been updated”
‘ListBox4.RowSource = “Vendors”  ‘for refreshing vendors listbox
End Sub

my range defined name is as follows: =OFFSET(Vendors!$A$1,1,0,COUNTA(Vendors!$A:$A)-1,8)

Reply | Quote

my range defined name is as follows: =OFFSET(Vendors!$A$1,1,0,COUNTA(Vendors!$A:$A)-1,8)

Abouya,

Are you sure you want the width of your dynamic range set to 8?

If I understand you correctly, you have 8 textboxes on a form that you enter values into. By clicking on a button, the values in the textboxes are sent to the dynamic range and therefore, your listbox, which is bound to the dynamic range, will update as well.

From the dynamic range formula that you posted and not having indicated number of columns in your listbox, I can only guess that this is either a single or dual column list box. This code will work if:

1. Your listbox is set to 1 column and your dynamic range is =OFFSET(Vendors!$A$1,1,0,COUNTA(Vendors!$A:$A)-1,1)
2. Your listbox is set to 2 columns and your dynamic range is =OFFSET(Vendors!$A$1,1,0,COUNTA(Vendors!$A:$A)-1,2)
3. You can have as many columns in your textbox as you want as long as the width in you dynamic range is set to the same number. So if your dynamic range that you posted was correct, you would need to set the number of columns for your listbox to 8.

Code:

Private Sub CommandButton14_Click() ‘change button for Vendors
    Range(“vendors”)(1) = UserForm3.TextBox1.Value
    Range(“vendors”)(2) = UserForm3.TextBox2.Value
    Range(“Vendors”)(3) = UserForm3.TextBox3.Value
    Range(“Vendors”)(4) = UserForm3.TextBox4.Value
    Range(“Vendors”)(5) = UserForm3.TextBox5.Value
    Range(“Vendors”)(6) = UserForm3.TextBox6.Value
    Range(“Vendors”)(7) = UserForm3.TextBox7.Value
    Range(“Vendors”)(8) = UserForm3.TextBox8.Value
    MsgBox “Item has been updated”
End Sub

Private Sub UserForm_Initialize()
ListBox4.RowSource = “Vendors”
End Sub

The formula in the statement: Range(“GLDepartment”)((.ListIndex * 2) + 2).Delete
was used in the previous code because you were clicking a row to change and we need a way to covert that listbox row (or ListIndex) to indexes in the named range. The scenario here is different because we are addressing each index directly by assigning it a value of a textbox.

I hope that clears things up a bit.

Maud

Reply | Quote

Hi Maud,

Thank you for responding, i really appreciate your help.

Listbox 4 has 8 columns,

After i double click an item in the list box, textboxes from 1 to 8 show the values of the row of the listbox item. i’m trying to be able to change the textboxes values then click “Change button” as a way to update the listbox values, from Colum A to H. for example listbox4 index will be updated with the changes i make for say textbox1 to textbox8.

With the code i posted, it just updates the first column. it’s like the code stops after Range(“vendors”)((.ListIndex * 8) + 1) = UserForm3.TextBox1.Value .

I attached a sample file.

I spend hours trying to figure out whats happening with this.

46772-Book1

Again, thank you so much.

Reply | Quote

For some reason, even updating the code with the one you provided, it didn’t want to work, after i closed the file and reopen, now it does work.

I really may loose my insanity with vba .

Reply | Quote

Abouya,

Thanks for posting your file. So you are attempting to perform the same function as your initial post with the selecting a list row in a list box, modifying it, then applying the modifications back to the listbox. The only difference is that this time, you are using an 8 column listbox instead of two.

As I explained in post #6, you have to convert the known ListIndex and column into the range index as before using the

Range(“named range“)((.listIndex * #cols) + range column)

where:
named range is the entire range of data on the sheet corresponding to the list rows of ListBox4.
#cols is the number of columns in ListBox4 which is the same number of columns in the named range.
range column is the number of the column in the range currently being written.

So I wrote the code to do what you are attempting for Commandbutton14. Here is what I came up with:

Code:

Private Sub CommandButton14_Click()
[COLOR=”#008000″]’==================================
‘CHANGE BUTTON FOR VENDORS
‘==================================
‘CHECK IF RECORD IS SELECTED[/COLOR]
If ListBox4.ListIndex = -1 Then
MsgBox “Choose an item”, vbExclamation
Exit Sub
End If
[COLOR=”#008000″]’———————————-
‘UPDATE RECORDS AND LISTBOX LIST BASED ON CHANGES[/COLOR]
With ListBox4
    Range(“vendors”)((.ListIndex * 8) + 1) = Me.TextBox1.Value
    Range(“vendors”)((.ListIndex * 8) + 2) = Me.TextBox3.Value
    Range(“Vendors”)((.ListIndex * 8) + 4) = Me.TextBox4.Value
    Range(“Vendors”)((.ListIndex * 8) + 5) = Me.TextBox5.Value
    Range(“Vendors”)((.ListIndex * 8) + 6) = Me.TextBox6.Value
    Range(“Vendors”)((.ListIndex * 8) + 7) = Me.TextBox7.Value
    Range(“Vendors”)((.ListIndex * 8) + 8) = Me.TextBox8.Value
End With
[COLOR=”#008000″]’———————————-
‘PROGRESS BAR UPDATE[/COLOR]
Call Main
MsgBox “Item has been updated”
End Sub

As I got past the first two lines of writing to the range, I realized that the code you wrote in post #10 is exactly the same as mine. After completing it, I tested it isolating it from the rest of your project and it worked great. Nice job Abouya. So I am not sure why you were having issues.

Maud

Reply | Quote

Maud,

Thank you for responding. so i think i might have discovered the issue but can’t find how to fix it. the code in the file i sent you works fine, but for some reason it doesn’t work in the master file. after loading the userfom and i double click an item in the listbox, values show up in the textboxes, i make few changes to try then click change button, nothing happens, just the first column changes. However, if i use the search field and search for a vendor then do some changes to the textboxes values, change button works.

it’s a very weird issue, i have no idea why such thing may happen. i created a new sheet and copied the Vendors values then created the range again but still have the same issue. only if i search a vendor using the search field renders the change button usable/workable!

Any ideas why this is happening?

Also,

Maud,

I also noticed another issue

Code:

 With ListBox1
                Range(“ExpenseType”)((.ListIndex * 2) + 2).Delete
                Range(“ExpenseType”)((.ListIndex * 2) + 1).Delete
            End With

Using this code above, especially to remove the last item in listbox pushes values in adjacent table to the left. In this spreadsheet i have two table named with two name ranges [GLDepartment]{Columns A&B} [ExpenseType]{Columns E&F}.

List box1 respresents [GLDepartment] and ListBox2 represents [ExpenseType].

When Running code above, especially to delete last item in list box1, [ExpenseType]{Columns E&F} move to the left to C and D columns. Please PM me and i can send you the file.

Thank you so much Maud.

Best Regards,

Reply | Quote

Abouya,

I PM’d you. Send the file and don’t worry, we’ll figure it out.

Maud

Reply | Quote