Yes, that's the two methods to make a control array: using an Array or a Collection.
I lean towards the Collection method because the .Add method has an optional Key parameter where you can stick the .Name property (for example) of the OLEObject and access members of the collection by name. The nice thing about that is that you can name it in the spreadsheet without lifting the lid on the code, thus smoothly feeding into the VBA domain from the user domain.
Another point is that you can generalise the events Class. You can make it so that you only need one class for multiple control types. You do this by having one of each control type in the events Class module (all declared "WithEvents") and using TypeName and a Select Case statement for the Let and Get on the control property of the events Class. In the Terminate method you can just set all of the objects to Nothing proceeded by an On Error Resume Next statement to ignore the error you get setting a Nothing object to Nothing.
|