A re-usable Form with a listbox filled with an array defined when .SHOW - how to define?

Hi. Hope someone can help a VB-dabbler get past this issue.

I want to create a Form that I can re-use multiple times within a main macro. The idea is that the form presents a set of options that can be multi-chosen, writes that back to a cell, then next time it is invoked presents a different set of options (and writes that response back to a different cell).

It's on changing the options that I'm getting stuck.


My test sheet has two defined ranges, "VideoCheck" and "AudioCheck" containing my data, and my form has a list box "lstWhatColumns" that provides the picking choice. I've created the form ("frmWhatColumns") and sorted the write-back, but the only way I can set the RowSource so that it works is to explicitly define the Range in the RowSource property of the listbox itself.

My (very crude) test code is as follows:

Please Login or Register  to view this content.

So long as the RowSource is explicitly defined with a Range name this works fine, but what I want to be able to do is - within the main macro - be able to pass the RowSource in to the list box at the point of calling.

I have been experimenting with frmWhatColumns.lstWhatColumns.RowSource = VideoChecks - but I cannot find where this needs to sit so that it sets the data.

Any assistance greatly appreciated.

Kind regards

Steve

Hi Kyle

That, sir, is a thing of beauty! Addresses my problem exactly.

I can progress with my experiments now. They have drawn up another issue, but as I spent ages trawling rather full threads to try and find this one little nugget of info I am going to post it separately, so that I can leave this as a self-contained response to one thing.

Many thanks once again.

Kind regards

Steve

Glad it helped

Hi Norie, it's largely sticking to convention and personal preference.

My coding (probably due to me working in C# and javascript most of the time) tends to be fairly object orientated, as such I tend to encapsulate process in the class it is in and I'll always prefer to interact with properties of that class than variables directly. As such I would (in the round) consider objects (controls etc) within a useform private and interact only with exposed properties/explicit public routines - If I'm feeling really pedantic I might even go as fas as to make an interface class for the userform

I also don't use global variables if I can help it, the usual userform falls firmly into this category in my opinion, I'd rather narrow the scope of the user form and have full control over its initialization and removal, this also allows me to have multiple instances of it open at once. Since I try to stick to a single convention for doing something so any code is consistent, I only ever initialize userforms in this manner, I always declare a new instance of them - it also allows me to check if they're nothing which can be handy in certain circumstances.

Pedantic Version of the above :

Please Login or Register  to view this content.

Form:

Please Login or Register  to view this content.

Interface:

Please Login or Register  to view this content.