ListFillRange

[Tendresse]

I have a ListBox object (from Controls toolbar) in Sheet1. The ListFillRange
property is a defined range named 'MyRange' (located in Sheet2). 'MyRange' is
dynamic. It changes in length according to some calculations. I want the
ListBox items to change accordingly. I'm using the following code to first
set the ListFillRange to nothing then set it to MyRange. However, the list
doesn't get refreshed straight away. The list items get updated only when i
manually activate a different worksheet then go back to Sheet1. How can i fix
that? is there such a thing as 'refreshing' the listbox programatically? i'm
using Excel 2003
Here is my code:

' delete the old range
ActiveWorkbook.Names("MyRange").Delete

[code to do some calculations]

' give the new range the same name
Sheets("Sheet2").Range("A5:B5").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Names.Add Name:="MyRange", RefersTo:=Selection

Sheets("Sheet1").activate

' assigning the newly named range to the listbox
Worksheets("Sheet1").OLEObjects("ListBox2").ListFillRange = ""
Worksheets("Sheet1").OLEObjects("ListBox2").ListFillRange = "MyRange"

exit sub

Everything goes fine except that the listbox doesn't show the new items
directly. Why do i have to log out of Sheet1 then go back to find the new
results in the listbox?
Any help will be greatly appreciated.
Cheers, Tendresse

 

[JLGWhiz]

Can't duplicate the problem. It fills on demand for me. One thing about
your code that I did have to change was this line:

Sheets("Sheet2").Range("A5:B5").Select

To:

Sheets("Sheet2").Activate
Range("A5:B5").Select

 

[JLGWhiz]

On second thought, after reading your post again, if you are expecting the
listbox to automatically add items as you add items to the named range, it
will not do that. It must be re-initialized to reload the ListFillRange.

 

[Tendresse]

Hi JLGWhiz
Thanks a lot for your reply. Well i'm still getting the same problem and i'm
desparate for help. I tried every single possible solution i could think of
with no result. I'm also having a few other glitches that are driving me
crazy. Because i can't pinpoint which part of the code exactly is causing the
trouble, it would be very hard to describe the code in a few words in here.
I'm wondering if there is any possibility i could email you my file so you
can have a better look and hopefully help me through it. If that's possible i
would be in debt for you forever.
Cheers, Tendresse.

 

[Tendresse]

Hi JLGWhiz,
I'll try to explain some more details hopefully they will clarify the
problem to you.
In Sheet1, i have some checkboxes for users to select from. Then there is a
commandbutton with caption 'Search'. The code behind this button searches for
the criteria selected by the users in the checkboxes and puts the results in
Sheet2 in 'MyRange'. The Listbox in sheet1 is then supposed to have its
ListFillRange property equal to MyRange.
All this goes well .. except that the items in the list don't get displayed
inside the listbox straight away. I found out that i first have to give the
screen a little 'tingle'. If i simply scroll down the screen and up again,
the list is there. Or if i minimize the window then maximize it, i can then
see the search results listed in the list box. It's as if the screen is not
refreshing its pixels unless it's 'tickled'!! I also noticed if i had another
application active on the screen in front of my sreadsheet, then i close this
other application, i can still visually see part of it within the listbox. Do
you know what i mean? Do you think this is a hardware problem related to the
capacity of the screen to refresh? or is it a programmatic problem with the
listbox? is there anyway i can go around this problem in my code. Is there
such a thing as: Screen.refresh?
PS i tried the spreadsheet in 3 other computers (fairly new ones) and the
same thing happens.
Thanks again for all your help.
Tendresse

On second thought, after reading your post again, if you are expecting the
listbox to automatically add items as you add items to the named range, it
will not do that. It must be re-initialized to reload the ListFillRange.

I have a ListBox object (from Controls toolbar) in Sheet1. The ListFillRange
property is a defined range named 'MyRange' (located in Sheet2). 'MyRange' is
dynamic. It changes in length according to some calculations. I want the
ListBox items to change accordingly. I'm using the following code to first
set the ListFillRange to nothing then set it to MyRange. However, the list
doesn't get refreshed straight away. The list items get updated only when i
manually activate a different worksheet then go back to Sheet1. How can i fix
that? is there such a thing as 'refreshing' the listbox programatically? i'm
using Excel 2003
Here is my code:

' delete the old range
ActiveWorkbook.Names("MyRange").Delete

[code to do some calculations]

' give the new range the same name
Sheets("Sheet2").Range("A5:B5").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Names.Add Name:="MyRange", RefersTo:=Selection

Sheets("Sheet1").activate

' assigning the newly named range to the listbox
Worksheets("Sheet1").OLEObjects("ListBox2").ListFillRange = ""
Worksheets("Sheet1").OLEObjects("ListBox2").ListFillRange = "MyRange"

exit sub

Everything goes fine except that the listbox doesn't show the new items
directly. Why do i have to log out of Sheet1 then go back to find the new
results in the listbox?
Any help will be greatly appreciated.
Cheers, Tendresse