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

 

[Steve_K]

Finally got what I wanted, although I'm sure it's pretty messy:

Sub btnSave_Click()
    Dim arrCols
    Dim arrLBs
    Dim collection
    Dim col As Long, row As Long
    
    Set generalColumns = New collection
    Set leadsColumns = New collection
    Set pipelineColumns = New collection
    Set backlogColumns = New collection
    Set premiumColumns = New collection
    
    arrCols = Array(generalColumns, leadsColumns, pipelineColumns, backlogColumns, 
    premiumColumns)
    arrLBs = Array("lbGeneral", "lbLeads", "lbPipeline", "lbBacklog", "lbPremiums")

    'Store the items from each listbox into a its own corresponding collection so it is easier to loop through
    For j = LBound(arrCols) To UBound(arrCols)
        With configForm.Controls(arrLBs(j))
            For i = 0 To .ListCount - 1
                If .Selected(i) Then
                    arrCols(j).Add (.List(i))
                End If
            Next
        End With
    Next j
    
    'Define and clear the table
    Set ws = Sheets("Config")
    Set table = ws.Range("Config")
    table.Clear
    
    'Set row and column indexes to 0
    col = 0
    row = 0
    'Loop through each collection for columns and each collection item is the row
    For Each collection In arrCols
        col = col + 1 'inside a column
        For Each collectionItem In collection
            row = row + 1 'inside a row
            table.Cells(row, col) = collectionItem
            table.Columns(col).AutoFit
        Next collectionItem
        row = 0 'reset the row count to start at the top of each column
    Next collection


    'Hide the form so we don't lose the selections while the sheet is open
    Me.Hide
    
End Sub

If anyone would care to offer suggestions on how I could refactor this to combine certain steps, I'd be much obliged.

Thanks!

 

[Norie]

You might want to look at placing the values from each collection in the table in one go.

I think that's possible, I know it is with arrays.

PS One tiny thing, you probably want to autofit after you've ented all the data, not after each item.

Well looks like I was wrong about writing a collection to a worksheet in one go - tried a few things and nothing worked.