VBA: Pass variables between user forms?

Steve_K

Board Regular
Joined
Dec 23, 2011
Messages
187
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
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Thank you, sir! Forgive my ignorance, but when you say declaring, are you referring to:

Code:
 Dim myCol As New Collection

And, in this case, would I simply have these statements outside of any Subs in the General Module?
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
FYI it is generally considered better practice to use
Code:
Public myCol As Collection
rather than
Code:
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,933
Messages
6,175,481
Members
452,647
Latest member
MatthewBiersay

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top