From: Joel on
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" wrote:

> There is data in that range
>
> "Joel" wrote:
>
> > 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" wrote:
> >
> > > I change it to :
> > > Sheets("Home").OLEObjects("ListBox1").ListFillRange =
> > > Range("wrkshtrng").Address
> > > The listbox is blank now.
> > > "Joel" wrote:
> > >
> > > > 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" wrote:
> > > >
> > > > > 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
From: ranswrt on
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" wrote:

> 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" wrote:
>
> > There is data in that range
> >
> > "Joel" wrote:
> >
> > > 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" wrote:
> > >
> > > > I change it to :
> > > > Sheets("Home").OLEObjects("ListBox1").ListFillRange =
> > > > Range("wrkshtrng").Address
> > > > The listbox is blank now.
> > > > "Joel" wrote:
> > > >
> > > > > 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" wrote:
> > > > >
> > > > > > 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
From: Joel on
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" wrote:

> 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" wrote:
>
> > 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" wrote:
> >
> > > There is data in that range
> > >
> > > "Joel" wrote:
> > >
> > > > 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" wrote:
> > > >
> > > > > I change it to :
> > > > > Sheets("Home").OLEObjects("ListBox1").ListFillRange =
> > > > > Range("wrkshtrng").Address
> > > > > The listbox is blank now.
> > > > > "Joel" wrote:
> > > > >
> > > > > > 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" wrote:
> > > > > >
> > > > > > > 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
From: ranswrt on
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.

"Joel" wrote:

> 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" wrote:
>
> > 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" wrote:
> >
> > > 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" wrote:
> > >
> > > > There is data in that range
> > > >
> > > > "Joel" wrote:
> > > >
> > > > > 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" wrote:
> > > > >
> > > > > > I change it to :
> > > > > > Sheets("Home").OLEObjects("ListBox1").ListFillRange =
> > > > > > Range("wrkshtrng").Address
> > > > > > The listbox is blank now.
> > > > > > "Joel" wrote:
> > > > > >
> > > > > > > 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" wrote:
> > > > > > >
> > > > > > > > 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
From: Dave Peterson on
Did you try all the other suggestions at your earlier post?

Just curious.

ranswrt wrote:
>
> 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

--

Dave Peterson