Prev: Re-Post: Embedded Word File
Next: Error 1004 setting xValues repeatedly
From: ranswrt on 18 Jul 2008 19:33
I have been stumped with a listbox added to a worksheet using the control
toolbox. I have been having problems refreshing the listbox when the data
range is changed. I have been using the following code to refresh it:

Sheets("name").OLEObjects("Listbox1").ListFillRange = Range("rng").address

I have been getting a blank listbox. I can manually enter the range in the
property window for the listfillrange, but when the range is changed the
listbox isn't updated. Is there a better way to update the listbox?
Thanks
From: Joel on 18 Jul 2008 20:03
See earlier posting the answer is

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


Using Range("rng").address doesn't include the sheet name. The only way of
getting the sheet name is using names, but names include an equal sign at the
beginning. the MID removes the equal sign.


"ranswrt" wrote:

> I have been stumped with a listbox added to a worksheet using the control
> toolbox. I have been having problems refreshing the listbox when the data
> range is changed. I have been using the following code to refresh it:
>
> Sheets("name").OLEObjects("Listbox1").ListFillRange = Range("rng").address
>
> I have been getting a blank listbox. I can manually enter the range in the
> property window for the listfillrange, but when the range is changed the
> listbox isn't updated. Is there a better way to update the listbox?
> Thanks
From: ranswrt on 18 Jul 2008 20:05
I just noticed that the address that is being put into the listfillrange
property from the code that I am using is from the same sheet. The
datarange is on a different sheet that the listbox.

I tried:

Sheets("name").OLEObjects("Listbox1").ListFillRange =
sheets("data").Range("rng").address

I still get the same results

"ranswrt" wrote:

> I have been stumped with a listbox added to a worksheet using the control
> toolbox. I have been having problems refreshing the listbox when the data
> range is changed. I have been using the following code to refresh it:
>
> Sheets("name").OLEObjects("Listbox1").ListFillRange = Range("rng").address
>
> I have been getting a blank listbox. I can manually enter the range in the
> property window for the listfillrange, but when the range is changed the
> listbox isn't updated. Is there a better way to update the listbox?
> Thanks
From: ranswrt on 18 Jul 2008 20:15
The sheet that contains the listbox is named 'Home'
The listbox name is 'Listbox1'
The sheet that contains the datarange for the listbox is named 'Current DB'
The name of the data range is 'wrkshtrng'

I noticed that when I ran the code you suggested in the earlier posts

Sheets("Home").OLEObjects("Listbox1").ListFillRange =
Range("wrkshtrng").address

The range for the ListFillRange changed to $D$6:$D$7 which is in the sheet
that contains the listbox. Not the sheet that contains the datarange.

So I tried

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

I got the same results. How do I get the address to be in the right
worksheet?

"Joel" wrote:

> See earlier posting the answer is
>
> Sheets("Home").OLEObjects("ListBox1").ListFillRange = _
> Mid(Names("wrkshtrng").Value, 2)
>
>
> Using Range("rng").address doesn't include the sheet name. The only way of
> getting the sheet name is using names, but names include an equal sign at the
> beginning. the MID removes the equal sign.
>
>
> "ranswrt" wrote:
>
> > I have been stumped with a listbox added to a worksheet using the control
> > toolbox. I have been having problems refreshing the listbox when the data
> > range is changed. I have been using the following code to refresh it:
> >
> > Sheets("name").OLEObjects("Listbox1").ListFillRange = Range("rng").address
> >
> > I have been getting a blank listbox. I can manually enter the range in the
> > property window for the listfillrange, but when the range is changed the
> > listbox isn't updated. Is there a better way to update the listbox?
> > Thanks
From: Joel on 18 Jul 2008 20:43
Does this test code get the correct worksheet?

Sub test()

With Sheets("current db")
Set xcell = .Range("B4")
num = 10
.Range(xcell.Offset.Offset(1, 0), _
xcell.Offset(num, 0)).Name = "wrkshtrng"
End With

a = Mid(Names("wrkshtrng").Value, 2)


End Sub


"ranswrt" wrote:

> I just noticed that the address that is being put into the listfillrange
> property from the code that I am using is from the same sheet. The
> datarange is on a different sheet that the listbox.
>
> I tried:
>
> Sheets("name").OLEObjects("Listbox1").ListFillRange =
> sheets("data").Range("rng").address
>
> I still get the same results
>
> "ranswrt" wrote:
>
> > I have been stumped with a listbox added to a worksheet using the control
> > toolbox. I have been having problems refreshing the listbox when the data
> > range is changed. I have been using the following code to refresh it:
> >
> > Sheets("name").OLEObjects("Listbox1").ListFillRange = Range("rng").address
> >
> > I have been getting a blank listbox. I can manually enter the range in the
> > property window for the listfillrange, but when the range is changed the
> > listbox isn't updated. Is there a better way to update the listbox?
> > Thanks
 |  Next  |  Last
Pages: 1 2 3
Prev: Re-Post: Embedded Word File
Next: Error 1004 setting xValues repeatedly