Run time Error

  • Thread starter Thread starter ranswrt
  • Start date Start date
R

ranswrt

I have a procedure the deletes a sheet and updates a listbox on a worksheet.
I get the following error when this part of the code is ran
"Sheets("Home").OLEObjects("ListBox1").ListFillRange = "wrkshtrng""

The error I get is this:

run-time error '-2147352567 (80020009)':
Could not set the listcursor property. Not enough storage is available to
complete this operation.

What could possibly be causing this?
Thankx
 
Why do you have two double quotes at the end of the line

AGAIN if "wrkshtrng" is a defined name then it should be

"Sheets("Home").OLEObjects("ListBox1").ListFillRange = _
Range("wrkshtrng").address
 
I change it to :
Sheets("Home").OLEObjects("ListBox1").ListFillRange =
Range("wrkshtrng").Address
The listbox is blank now.
 
Now go to your worksheet menu

Insert - Name - Define

Then click on "wrkshtrng"

Next check if there is any data in the address defined by "wrkshtrng"
 
There is data in that range

Joel said:
Now go to your worksheet menu

Insert - Name - Define

Then click on "wrkshtrng"

Next check if there is any data in the address defined by "wrkshtrng"
Click to expand...
 
You are going to have to look at the properties in the Listbox.

1) go to spreadsheet menu view - toolbars - Control toolbox
2) Press the triangle on the toolbar to go into Design Mode. Design mode
button is a toggle button the Enters/Exit.
3) right click Listbox and select properties. Look at ListRange propertiy
to see if it is correct
4) Press triagle again to exit Design Mode.
 
It show ListFillRange as $D$6:$D$7 which is the correct cell names, but I
don't know if it refers to the right sheet. The sheet with the range that I
want to get the data from is different than the sheet that contains the
listbox.
 
did you manually define the range or did you do it by code.

Manually: go back to menu Insert - Nme - Define and reselect the Range with
the correct worksheet.

Code : add sheet name

"=sheet1!$D$6:$D$7
 
Earlier in the code I am naming the range with:

Sheets("current db").Range(xcell.Offset.Offset(1, 0), xcell.Offset(num,
0)).name = "wrkshtrng"

This is done after an item has been removed from the range by deleting the
row that it is in. Latter in the procedure I use:

Sheets("Home").OLEObjects("ListBox1").ListFillRange =
Range("wrkshtrng").Address

and my listbox is still blank.
 
Her is the best answer. Names returns the full address but contains an equal
sign at the beginning. The MID() will remove the equal sign.

Sheets("Home").OLEObjects("ListBox1").ListFillRange = _
Mid(Names("wrkshtrng").Value, 2)
 
Back
Top