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

 

[Norie]

It's not complicated at all to post the data to a worksheet.

Which range is your table in?

Is it actually a table?

If it is you can use something like this.

Private Sub CommandButton1_Click()
Dim tbl As ListObject
Dim rw As ListRow
    Set tbl = Worksheets("Sheet1").ListObjects(1)
    
    Set rw = tbl.ListRows.Add
    
    ' change control names here if required
    rw.Range.Value = Array(ComboBox1.Value, Combobox2.Value, TextBox3.Value, TextBox3.Value, TextBox4.Value, TextBox5.Value)
    
End Sub

 

[Steve_K]

Hi Norie,

Thanks for your reply, the more I read, the more I begin to understand. Yes, it's a table called "Opportunities". So far, this is what I'm trying. It's probably pretty far off, but I wanted to make sure I gave it a go before asking for more help...

tableColumnCount = Range("Opportunities[#All]").Columns.Count

For j = LBound(arrCols) To UBound(arrCols)
        For i = 0 To tableColumnCount
            With ws.ListObjects("Opportunities")
                If j = i Then
                    .ListColumns(i).DataBodyRange.Value = arrCols(j).item.Value
            End With
        Next i
    Next j

Also, I'm using the code you so kindly provided a couple days back to actually populate the items from my list boxes into their respective collections:

    'Loop through the list boxes and add the selected items to each collection
    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

Would it be better to either a.) populate each cell of each table as the collections are populated, b.) forego using collections altogether and just populate the tables and use them, or c.) populate the collections AND populate the tables? My gut says B...

 

[Norie]

What exactly do you want to populate the table with?

I assumed it was what the user had selected or entered in textboxes/comboboxes/listboxes/whatevercontrol.

I thought the collections you have were being used to populate the listboxes.

 

[Steve_K]

Sorry, I'll explain:

I'm creating a system which keeps track of sales opportunities. An opportunity row has several columns, each split into one of 5 different phases: General, Leads, Pipeline, Backlog, Premiums. To make the table easier to navigate, I want to be able to allow a user to hide specific columns per each phase. My form with list boxes is used for that: each list box for each phase reads in the headers from the large opportunity table and then the user selects which columns he wants to be visible in each phase. Once he hits "Save" on the user form, his choices should be made persistent and then the Opportunities table will read and hide them accordingly.

Originally, I thought I could use collections to keep the user's list box choices persistent. That works so long as the document is open, but once the document is closed, the choices would be lost. To solve that, I reckoned I'd need to populate the choices for each list box into a separate "Config" worksheet to save them. Since I like to keep things organized, I created a table for this on a separate sheet with a column for the choices from each list box. So, now I'm trying to figure out how to get the choices from each list box populated into the right column in the config table. Then I'll reason out how to read them and hide them.

Does that make any sense?

 

[Norie]

So you do want to populate a table with the selections from the listboxes?

Have you tried the code I posted, changing/adding control names as needed?

 

[Steve_K]

So you do want to populate a table with the selections from the listboxes?

Have you tried the code I posted, changing/adding control names as needed?

Yeah, I guess that's the easiest way to say it. Are you referring to the following code?:

    'Loop through the list boxes and add the selected items to each collection
    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

If so, I figured this was where things needed to be changed. I think I need to change that first line (arrCols) which refers to the array of Collections? Seems like it should be an array of table columns instead? I apologize if I'm a bit dense - to be honest, I'm still new to VBA and fumbling around a bit with some basics.

Thanks for your patience, I really appreciate it.

 

[Norie]

I'm referring to the code I posted in post #11 to populate a row in a table.

Looking at the code you posted I'm wondering if the listboxes are multiselect.

If they are what exactly do you want to put in the table?

All the selected values from each listbox?

 

[Steve_K]

Oh, good grief. I completely missed your post #11. I'm going to work with that a bit and see if I can't figure it out. Sorry about that.

 

[Steve_K]

To better illustrate what I'm after as I try out your previous post:

Fig 1: Master Opportunities table:

[TABLE="width: 800"]
<tbody>[TR]
[TD]Added Date[/TD]
[TD]Opp Type[/TD]
[TD]Client[/TD]
[TD]Contact Method[/TD]
[TD]Amount[/TD]
[TD]Est. Start Date[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]10/20/2012[/TD]
[TD]Lead[/TD]
[TD]Nike[/TD]
[TD]Phone[/TD]
[TD]$10,000[/TD]
[TD]10/25/2012[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]10/26/2012[/TD]
[TD]Backlog[/TD]
[TD]Coach[/TD]
[TD]E-mail[/TD]
[TD]$20,000[/TD]
[TD]10/30/2012[/TD]
[TD]...[/TD]
[/TR]
</tbody>[/TABLE]

Fig 2: List boxes from user form, triggered from "Opportunities" worksheet and populated with the headers from the table in Fig. 1. Red items are the fields the user has selected per list box.

[TABLE="width: 800"]
<tbody>[TR]
[TD]General[/TD]
[TD]Leads[/TD]
[TD]Pipeline[/TD]
[TD]Backlog[/TD]
[TD]Premiums[/TD]
[/TR]
[TR]
[TD]Added Date[/TD]
[TD]Added Date[/TD]
[TD]Added Date[/TD]
[TD]Resource[/TD]
[TD]Added Date[/TD]
[/TR]
[TR]
[TD]Opp Name[/TD]
[TD]Opp Name[/TD]
[TD]Opp Name[/TD]
[TD]Opp Name[/TD]
[TD]Opp Name[/TD]
[/TR]
[TR]
[TD]Client[/TD]
[TD]Client[/TD]
[TD]Client[/TD]
[TD]Client[/TD]
[TD]Client[/TD]
[/TR]
[TR]
[TD]Contact Method[/TD]
[TD]Contact Method[/TD]
[TD]Contact Method[/TD]
[TD]Contact Method[/TD]
[TD]Contact Method[/TD]
[/TR]
[TR]
[TD]Amount[/TD]
[TD]Amount[/TD]
[TD]Amount[/TD]
[TD]Amount[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]Est. Start Date[/TD]
[TD]Est. Start Date[/TD]
[TD]Est. Start Date[/TD]
[TD]Est. Start Date[/TD]
[TD]Est. Start Date[/TD]
[/TR]
</tbody>[/TABLE]

Fig 3: Config Table on "Config" worksheet. The User Form in Fig 2 should list these as "selected" after it populates all of the headers from the table in Fig. 1.

[TABLE="width: 800"]
<tbody>[TR]
[TD]General[/TD]
[TD]Leads[/TD]
[TD]Pipeline[/TD]
[TD]Backlog[/TD]
[TD]Premiums[/TD]
[/TR]
[TR]
[TD]Opp Name[/TD]
[TD]Added Date[/TD]
[TD]Opp Name[/TD]
[TD]Resource[/TD]
[TD]Added Date[/TD]
[/TR]
[TR]
[TD]Client[/TD]
[TD]Opp Name[/TD]
[TD]Amount[/TD]
[TD][/TD]
[TD]Opp Name[/TD]
[/TR]
[TR]
[TD]Amount[/TD]
[TD]Client[/TD]
[TD][/TD]
[TD][/TD]
[TD]Est. Start Date[/TD]
[/TR]
</tbody>[/TABLE]

Hope that makes more sense.

 

[Steve_K]

So I've been playing at this for a while, and I think I'm close, but still just a bit stuck. Here's what I have:

 Set ws = Sheets("Config")
 Set tbl = ws.Range("Config[#All]")
oppTypes = Array("General", "Leads", "Pipeline", "Backlog", "Premiums")
arrLBs = Array("lbGeneral", "lbLeads", "lbPipeline", "lbBacklog", "lbPremiums")
    
    tbl.Clear
    
    'Loop through the list boxes and add the selected items to each collection
                For j = LBound(oppTypes) To UBound(oppTypes)
                    With configForm.Controls(arrLBs(j))
                        For i = 0 To .ListCount - 1
                            If .Selected(i) Then
                                tbl.Cells(j, i).Value = .List(i)
                            End If
                        Next i
                    End With
                Next j

When I tested the For loop values, it seemed like I was getting the right row and column values for inserting the list box values. At the moment, I get the following error: Run time error '1004': Application or Object defined error." Any thoughts on what I'm missing? Will continue to search...

Thanks for your time!

EDIT: Seems I was mistaken - the row count (i) is not quite what it needs to be.