Run time Error

[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

 

[Joel]

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

 

[ranswrt]

I change it to :
Sheets("Home").OLEObjects("ListBox1").ListFillRange =
Range("wrkshtrng").Address
The listbox is blank now.

 

[Joel]

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"

 

[ranswrt]

There is data in that range

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"

 

[Joel]

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.

 

[ranswrt]

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.

 

[Joel]

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

 

[ranswrt]

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.

 

[Dave Peterson]

Did you try all the other suggestions at your earlier post?

Just curious.

 

[ranswrt]

Yes I did

Did you try all the other suggestions at your earlier post?

Just curious.

 

[Joel]

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)

 

[Dave Peterson]

I didn't see it in any of your followup posts.

Yes I did