I have a problem that my google-fu is not helping me solve.

I inherited a model in Excel that is absolutely horrible (210 worksheets, user input is atrocious, the math is suspect, etc).

There is one sheet for the user input. Currently, it is all manual (you have to type in the platform name, exactly). I am changing that so that a ListBox presents the valid choices.

The items to populate that ListBox are on another sheet, each ListBox has its own sheet that has the actual data. On "UserInput" there are eight different ListBoxes.

To populate the ListBoxes, each data worksheet has a Change event that triggers. This routine just sets the ListBox name, sheet, and the row/column that the data starts in (because the sheets aren't consistent). Then the items are populated by reading across the row, each column is a unique name with the data used by the model below it.

I'm having problems passing the ListBox object from that subroutine to the one that does the actual populating (so I don't have to copy the routine 8 times).


That works fine when LiftListBox doesn't have a selection in it. As soon as it has a selection, lstbox gets set to the selection instead of the object itself.

I've tried using OLEObject.Objects and everything else I could find on the net, all with the same results. It works fine as long as there is no selection, but as soon as there is, it passes the selection.

Anybody know what I am missing (besides saying screw it and starting over in FORTRAN )?