VBA: Pass variables between user forms?

[Steve_K]

Hi all,

I have a user form which takes values from a listbox and stores them in a collection when I click a command button. I'd like to be able to use that collection in another user form in the same workbook. What's the best way to make the collection from the first user form available for use in the second user form, since both forms contain private subs for their respective controls?

Thanks for reading!

Best,
Steve

 

[Andrew Poulsom]

Try declaring the collection as Public in a General module, removing any other declarations.

 

[Steve_K]

Thank you, sir! Forgive my ignorance, but when you say declaring, are you referring to:

 Dim myCol As New Collection

And, in this case, would I simply have these statements outside of any Subs in the General Module?

 

[Andrew Poulsom]

At the top of a General Module:

Public myCol As New Collection

 

[Steve_K]

Thanks, it works beautifully.

 

[Steve_K]

It occurs to me that storing the value in a collection may not work as well as I'd thought. The entire point of the collections is to hold "saved" information. It works great while the document is open, but once the document is closed, the data is lost and needs to be re-selected and saved into the collection. Would the better option just be to save the choices into another sheet rather than a collection?

 

[Andrew Poulsom]

If you Hide the UserForm rather than Unloading it, selections will be maintained while the workbook is open. To retain selections between sessions I would store them on a worksheet.

 

[RoryA]

FYI it is generally considered better practice to use

Public myCol As Collection

rather than

Public myCol As New Collection

so that you can better control and determine at what point the variable is initialised. It also thereby makes debugging easier.

 

[Steve_K]

Most excellent, thanks to you both for the information.

 

[Steve_K]

Not sure if it makes sense to post this as a new thread, but it's related to the question about "saving" data I asked earlier, so I kept it here.

I have a user form with 5 list boxes and an empty table with 5 empty columns (with headers that correspond to the list boxes). When I "save" the form, I'd like to populate each of the five columns in the table with the options chosen from their corresponding list boxes. I've been googling for ways to write data to sheets and it seems a bit complicated, so I thought I'd try to get some advice here while I look into it. I know I could use .Cells(), but I thought maybe there was an easier way to specify a table column and then write the values into the column without specifying its exact sheet location...

Thanks again for your time -- much-appreciated.