listbox

[ranswrt]

I have a listbox that I added to a worksheet. I also have a procedure that
adds items to the range for the listbox. I need to update the listfillrange
so that the new items are added to the listbox. I used the following code:
Sheets("Home").Shapes("ListBox1").ListFillRange = "wrkshtrng"
This doesn't seem to be working. What am I doing wrong?
Thanks

 

[Norman Jones]

Hi Ranswrt,

The ListFillRange property requires a string
representing an address.

If wrkshtrg is a range variable, try something
like:

Sheets("Home").Shapes("ListBox1").ListFillRange = _
wrkshtrng.Address

If is a named range, try:

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

 

[Joel]

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

 

[Dave Peterson]

I would be specific where that range exists:

.... range("wrkshtrng").address(external:=true)

 

[ranswrt]

I tried the code that you suggested and it wouldn't work. I changed it to:
Sheets("Home").OLEObjects("ListBox1").ListFillRange = "wrkshtrng"
and that worked except the listbox showed the gridlines from the range and
wouldn't show the scroll bar until I scrolled the worksheet with my mouse
scroll. The listrange is on another worksheet from the listbox. Also the
listbox wouldn't show the last item in the range. Any ideas what I can do?

 

[Joel]

I not familar with all the options in a listbox. I think you have some
option set that I'm not familar with. Try adding a new listbox and see if
the same problem occurs. Then compare the properties of the two listboxes.

to see the properties of the list box you need to go into design mode.

1) Add control toolbox toolbar to the worksheet
2) Press the Triangle. the Triangle is a toggle option that switches you in
and out of design mode
3) right click Listbox and select properties.
4) Change options manually. Any option in the properties window can be
changes using VBA code.

When you are done press the triangle again to exit design mode and retry the
code.

View - Toolbars - Control Toolbox