Excel 2106 VBA: Sending Data from Userform, most efficient method

[jedwardo]

Hello everyone,

Been working on an automated bidding spreadsheet to put on a tablet for quoting projects... since 2012 haha. You guys have been indispensable and I just started working back on it again for the first time in a year so use your kids gloves with me please. I've forgotten everything I learned already. I have my input set up on the userform and am looking to take the data from the userform to the worksheet. When I found my old file I started over without the hundreds of textboxes, combobuttons and spinbuttons and opted for tabstrips. Much cleaner now and not crashing excel anymore.

My question is what is the most efficient method of transfering this data from the userform to the spreadsheet? I had been working at putting it all in a listbox which I was then going to transfer to the spreadsheet to organize but started thinking maybe it would be easier just to send it straight to the spreadsheet and then create a listbox from that range for viewing within the userform while bidding the project.

Here's a pic of what's going on at the moment:

So I have everything from the lower tabstrip going to that listbox on the right. Was planning on sending to the worksheet after making through the room but started thinking it might be easier just to send straight to the worksheet. My main concern is whatever is easier on the system since it's going to be on a tablet. My last versions of this bogged my 3820 with 24 gb ram down like no ones business so trying to make it as resource friendly as possible. Thoughts?

Thanks,
Jordan

 

[jedwardo]

old version that was killing my computer between the button heavy userforms and my nonsensical worksheets:

[/IMG]

 

[Norie]

If you've been able to populate the listbox then it should be pretty straightforward to put the data directly on a sheet and cut out the middle man.

If you wanted to keep the listbox, and the code you are populating it with, it would be easy to transfer from it to a sheet using it's List property.

Do you currently have code for populating the listbox?

 

[jedwardo]

I'm mid populating that actually. I've got the first line transferring at the moment but haven't sorted new additions to not overwrite that first line. I remember my code for copying the data from the old userform to worksheet got pretty hefty and was worried copying from listbox to worksheet after each room was fully populated might get bloated.

my code on the listbox:

Function PopList(lstbox As MSForms.ListBox, var As Variant)

With ufRooms.lbxBody
        If Not IsEmpty(var) Then
            .Clear
            If UBound(var, 1) = 1 Then
                .AddItem
                .List(0, 0) = var(1, 1)
                .List(0, 1) = var(1, 2)
                .List(0, 2) = var(1, 3)
                .List(0, 3) = var(1, 4)
                .List(0, 4) = var(1, 5)
                .List(0, 5) = var(1, 6)
                .List(0, 6) = var(1, 7)
                .List(0, 7) = var(1, 8)
                .List(0, 8) = var(1, 9)
                
            Else
                .List = var
            End If
            .ListIndex = -1
        End If
    End With
    
End Function

Private Sub UserForm_Activate()
    Call CreateListBoxHeader(Me.lbxBody, Me.lbxHeader, Array("Room", "Operation", "Type", "Quantity", "Price", "Prime", "Caulk", "Putty", "Stain"))
End Sub

Private Sub cbWoodReturn_Click()
Dim arrList
Dim oTab As MSForms.Tab
Dim var As Variant
    
   With ufRooms
    If .tsWoodRun.Value = -1 Then: .cbWoodReturn.Visible = False: GoTo fin
    
'    'Load Items to an Array
    arrList = Array(.tbxRooms.Text, .tsWood.SelectedItem.Caption, .tsWoodRun.SelectedItem.Caption, .tbxWoodQuant.Text, .tbxWoodPrice.Text, .tbWoodPrime.Tag, .tbWoodCaulk.Tag, .tbWoodPutty.Tag, .tbWoodStain.Tag)
'    'Load an Array Items to ListBox
    Worksheets("Lists").Range("TestList") = arrList   'lbxBody.List = arrList
    End With