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 )?

What version of Excel? VBA changed a bit between some of the versions.

Any chance you can attach a quick sample spreadsheet?

I've got a ton of experience with VBA in Access, but almost none in Excel, but it can't be too different. Are you passing the listbox byVal or byRef? If you haven't specified, it defaults to byRef (which is what I believe you want).

Is the little attached sample what you are trying to accomplish?

Without seeing the spreadsheet itself I can't be sure, but is this the old problem of trying to reference the object directly instead of making a copy of the object?

For instance in most computer languages if you say:

// Create first object
Object myFirstObject;
// (... populate the object with data ...)

// Create the second object
Object mySecondObject;
// Copy data from the first object to the second object so I get a starting point for my data
mySecondObject = myFirstObject;
// (... modify the data in the second object...)

You might think that you've just made a copy of the first object, and expect to access them separately, so that you can modify the data in them independently.

But that's not what you've done. What you just did was just assigned the first object's *pointer* to the second object, so, now *both* objects are pointing to the actual data set from the first object. They're now just two different names for the same thing, and you've completely lost the pointer to the original second object.

Not sure how it's handled in VBA, but it's possible that a simple "=" sign isn't good enough in the case where you're trying to make a copy. There might be a shortcut to accomplish what you need, or, you might have to instantiate each object separately and write a routine to copy the data from each one over.

This is just a guess, maybe it's something totally unrelated. I'd have to look at your code to know exactly. I've done some quite advanced programming in VBA for Word, but I've never tried to copy a list box like that.

Is it as simple as:

Dim lstbox As MSForms.ListBox
Set lstbox.Items() = Sheets("UserInput").LiftListBox.Items()

or something like that?

I'm using Excel 2013, and typically I use the default ByVal/ByRef (I haven't needed to change it yet).

The attached file is similar to what I am trying to do (really simplified).

When you open the workbook, it is supposed to populate the listboxes and then select the item you had previously selected.

Then, when you change one of the data sheets, it is supposed to refill in the listbox associated with that sheet, since you might have added or removed a platform choice.

When you select an item in one of the listboxes, it not only links to the cell on UserInput, but also on the data page associated with that listbox.

The populating works, the selection works, but once there is a selection made, it breaks because it sets Sheets("UserInput").AttackListBox to the selected value, where previously it was the object itself.

The formatting is all hosed since I ripped the code from the real model (which, sadly, I can't share).

I'm not sure if I'm really trying to copy the ListBox (I might be, objects are baffling ).

I'm just trying to simplify the code by using a subroutine and a With statement as opposed to typing out all the code for each listbox (since there are 8 of them). And since the populate routine happens a couple times (on workbook open and worksheet change), that would multiply the number of times I'd have to write it again.

The example I posted does what you want to do. I'm having a bit of trouble getting your Test file to work on Excel 2011 for Mac. Doesn't seem to like the ActiveX controls, plus I think there are other problems with the code.

You would just need to put the code on events instead of buttons.

If there are errors, it wouldn't shock me in the least.

I'll see what else I can come up with.

Thanks.