Populate a listbox on a userform with a dynamic named range
Posted on 2007-04-07
Using Excel 2003 I have created a userform with a couple of listboxes.
These listboxes are populated with lists whose lengths vary depending on other variables.
When I first created the the wolrkbook I was using named ranges (input by insert->names within excel, not vba). Now I want to modify the workbook (for efficiency and general tidyness) to use dynamic named ranges.
I have now removed my name definitions from within excel and I have begun to declare them in the Workbook_Open() sub using code such as:
ActiveWorkbook.Names.Add Name:="Manu", RefersTo:= _
When I want to update the listbox contents with the data in the dynamic named range "Manu", I use the following code:
ListBox2.List = Range("Manu")
This all worked fine when I used static named ranges, but now I have redefined them as dynamic I get the following error when the userform is created:
Run-time error '381'
Could not set the list property. Invalid property array index.
I'd be grateful if anyone could point me in the right direction for populating a listbox within a useform with a dynamic named range.