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").ListFi llRange = ""
Worksheets("Sheet1").OLEObjects("ListBox2").ListFi llRange = "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