Listbox initialized on workbook load

[Guest]

I have a listbox on a spreadsheet (not a user form) that is populated via the
listfillrange property. I can make selections and save them. When I open the
workbook, Excel initializes the listbox object and removes my selections. Is
there a way to disable this "feature"?
Thanks!

 

[Tom Ogilvy]

Use the saved selections to re-establish your selections in the listbox.

 

[Guest]

Hi Tom,
Saved selections does not appear to be a feature of Excel. If it is where
do I find it?

Thanks,
Kandi

 

[Tom Ogilvy]

You originally said:

I can make selections and save them.

You would make a record of them someplace in your workbook - perhaps on a
hidden sheet.

Or in the source data, perhaps put an X in an adjacent column. Then read
back the information in a macro and make the selections in the listbox -
perhaps in the workbook_open event or the sheet activate event.

http://www.cpearson.com/excel/events.htm
Chip Pearson's page on events.

 

[Guest]

The problem is that Excel automatically rebinds the objects to the fill list
removing any selections that were saved in the file prior to any macro code
running. So how do you stop this from happening? Does Excel have an option
that you can turn off so the object is not refreshed every time the workbook
is opened?

This would be simple if I had just one or a even few listboxes. I have many
and each refer back to the same feeder data of about 20 selections.

It would be really great if the objects were handled like links so you could
tell Excel not to update the links and avoid the refreshing of the selections.

 

[Tom Ogilvy]

I am not aware of any single setting or combination of settings that will do
this.