Hello,

I'm currently using Excel to store an evaluation table of sorts. Each row in the
first worksheet corresponds to the evaluation subject and each column represents an evaluation criterion. Some of these criteria are numeric, others are strings, others are a single choice dropdowns (like yes/no) via validation lists but some need multi-selection from the user. However, Excel doesn't seem to support this natively. So, I decided to use ActiveX Listbox objects to perform this operation.

The initial problem was keeping the user selections, even when the workbook is closed. Each criterion can have a listbox type associated which can have an arbitrary number of entries. This means that if a new evaluation subject row is added, it will share the same listboxes types as the previous one (i.e. if there are three criterion with listboxes of 5, 7 and 3 entries, this new subject will also have three listboxes with the same number of entries). What changes for each subject are the active selections for each listbox.

I came up with this algorithm: I intercept the close event (Workbook_BeforeClose), scan each of the affected listbox selections and store them in a storage sheet with true or false values. Then, I also intercept the open event (Workbook_Open) and I populate the listboxes (with values located in a entry worksheet) and fill them with the previously stored selections (in the storage sheet).

I'm not a VBA programmer, nor have I ever tried using ActiveX or OLE objects in the past before, but the above seems to work flawlessly. However, I cannot help but feel that there must be a better solution. The previous method performance degrades with the number of existing listboxes as well as the number of entries in each listbox, both at read (open workbook) and write (close workbook) time. Granted that this will only have a significant impact if the previous numbers are REALLY large, but nevertheless it doesn't seem like an elegant solution.

So, is there any way for the ActiveX listboxes to keep an internal state? That can be saved and restored at will? This would help for other stuff, since I also have to reset the listboxes properties (like width and height) at load time (it would be nice to be able to do this only once)? Or is there a different alternative altogether?

Thanks in advance,

Frederico Jerónimo