Loop through values in userform to store in array

D34THROW · Jul 27th 2016

Good morning,

My company is pulling away from QuickBooks but the ERP we are using has no capability to create quotes, so I'm creating an Excel to manage quotes. To this end, I have a userform to create a quote, with a MultiPage for Job Info, Materials, and Labor.

The Materials page has 21 line items, each with 5 components (a combo box and 4 text boxes per line).

Code

cmb_Materials_Line1_Item | txt_Materials_Line1_Color | txt_Materials_Line1_Qty | txt_Materials_Line1_Price | txt_Materials_Line1_Extended

...and so on through Line21.

What I'm trying to figure out is this: Assuming I create an array 'materials(20,4)', is there a way to loop through the lines, line by line, to populate the array or do I need to individually assign each value from the control to the corresponding element of the array? I then want to loop through the array itself to populate the line items on the estimate form.

I have attached the workbook in question if it helps.

I'm still fairly new to VBA as you can probably tell by the code in the userform, so I have some grasp of things but I'm not perfect yet!

Thank you so much for any help you can provide; I really appreciate it! If there's any more information I can provide, please let me know.

forum.ozgrid.com/index.php?attachment/69789/

apo · Jul 28th 2016

Re: Loop through values in userform to store in array

Hi..

Must admit.. I haven't played with Userform controls for a little while..but.. here is my take..

Quote

is there a way to loop through the lines, line by line, to populate the array or do I need to individually assign each value from the control to the corresponding element of the array?

You could loop through the controls on the userform.. assigning them to an array.. but it appears the controls have been added 'haphazardly'.. and therefore the loop will not flow the way you want it to..
If you look at the Tab Index property of some of the Controls... they are all over the place.. and i think when looping. it will follow this same flow..

Might be easier to remove all the controls and re-add them in the sequence you would like the loop to go in.. left to right.. top to bottom..

Look here to see how to loop through a Userforms controls..
http://www.ozgrid.com/VBA/control-loop.htm

One other quick thing i noticed..
In a line like this:

Code

Dim custFirstName, custLastName, custAddress1, custAddress2, custCity, custZip, custPhone1, custPhone2, custEmail, estName As String

Only the estName will be dimmed as String.. the rest will be dimmed as Variant..

D34THROW · Jul 28th 2016

Re: Loop through values in userform to store in array

Quote from apo;774623

Only the estName will be dimmed as String.. the rest will be dimmed as Variant..

That's actually really handy to know since a lot of my code declares variables that way.

As to the controls, would changing the TabIndex of each one to the flow I want be a solution or will I have to remove and re-add all the controls piecemeal? Also are tab indices specific to, say, a page of a MultiPage or will tabbing off the last control on Page1 automatically send you to TabIndex 0 on Page2 of the MultiPage?

apo · Jul 28th 2016

Re: Loop through values in userform to store in array

Hi..

Quote

As to the controls, would changing the TabIndex of each one to the flow I want be a solution or will I have to remove and re-add all the controls piecemeal?

To be honest.. and like i said.. Userforms are not my forte... so i am not sure on that one.. I just know that when i executed some code that looped through your controls on that page of your Mutli page.. i noticed that it did not populate the array the way you (i) would want it to... and the Tab Index kinda followed my gut feeling.. others more knowledgeable on this might interject..

So. put it this way.. in my mind.. you either add (re-add) the controls in a logical fashion (as per how you would like your array populated).. and all would be hunky dory.. or.. if your up for a challenge..(and this is not advisable and much more work.. but possible).. you need to loop through the controls and chuck the Choose function in there... nominating the tab index of each control as the value in the 'haphazard' sequence that yous tarted with..)..

Not sure if that make sense.. let me know if not..

**norie** · Jul 28th 2016

Re: Loop through values in userform to store in array

The controls seem to be consistently named so you should be able to loop through them using their names.

Actually, I'll take that back, some of the controls are named consistently, specifically the first 3 rows on the Materials tab.

If all the controls were named consistently you could use something like this.

Code

Dim arrMaterials(1 To 21, 1 To 5)
Dim I As Long


   
    For I = 1 To 21
        arrMaterials(I, 1) = Me.Controls("cmb_Materials_Line" & I & "_Item").Value
        arrMaterials(I, 2) = Me.Controls("txt_Materials_Line" & I & "_Color").Value
        arrMaterials(I, 3) = Me.Controls("txt_Materials_Line" & I & "_Qty").Value
        arrMaterials(I, 4) = Me.Controls("txt_Materials_Line" & I & "_Price").Value
        arrMaterials(I, 5) = Me.Controls("txt_Materials_Line" & I & "_Extended").Value
    Next I

Display More

By the way, have you considered using a multi-column listbox, one line of controls and a command button for materials?

The user would select/enter data in the line of controls and then click the command button to add them to the listbox.

You could also have buttons to edit/delete rows in the listbox.

With that setup it would be easier to work with the data on the Materials tab.

D34THROW · Jul 29th 2016

Re: Loop through values in userform to store in array

Quote from norie;774647

The controls seem to be consistently named so you should be able to loop through them using their names.

Actually, I'll take that back, some of the controls are named consistently, specifically the first 3 rows on the Materials tab.

If all the controls were named consistently you could use something like this.

That was the eventual plan, I just haven't had 20 minutes to change around all the control names.

Quote

By the way, have you considered using a multi-column listbox, one line of controls and a command button for materials?

The user would select/enter data in the line of controls and then click the command button to add them to the listbox.

You could also have buttons to edit/delete rows in the listbox.

With that setup it would be easier to work with the data on the Materials tab.

Display More

That was a solution I hadn't even considered and looks MILES easier than what I'm trying to do. I'm guessing I'd want to use nested For loops to step left to right and down and assign the proper values in the array? Thank you so much!

**norie** · Jul 29th 2016

Re: Loop through values in userform to store in array

Not sure about the nested loop thing, what would that be used for?

If you were using a listbox you can put all the data in an array in one go like this.

Code

Dim arrData()


    arrData = ListBox1.List
    
    ReDim Preserve arrData(UBound(arrData), ListBox1.ColumnCount - 1) ' resize to remove Nulls

D34THROW · Jul 29th 2016

Re: Loop through values in userform to store in array

Quote from norie;774696
Not sure about the nested loop thing, what would that be used for?

If you were using a listbox you can put all the data in an array in one go like this.
Code
Dim arrData()


    arrData = ListBox1.List
    
    ReDim Preserve arrData(UBound(arrData), ListBox1.ColumnCount - 1) ' resize to remove Nulls

:shocked:

The For loop WAS for that. That's a powerful control right there and perfect for what I'm trying to do. As far as writing that data to the actual worksheet, is there a way to create an array of cell addresses and then copy arrData to that or will THAT require a For loop to step through the array and copy data a cell at a time?

**norie** · Jul 29th 2016

Re: Loop through values in userform to store in array

It depends where you want to put the data.

If we extend the last code I posted this will put the contents of the listbox on Sheet1 starting at A1.

Code

Sheets("Sheet1").Range("A1").Resize(UBound(arrData, 1) + 1, UBound(arrData, 2) + 1).Value = arrData

D34THROW · Jul 29th 2016

Re: Loop through values in userform to store in array

The data is going into non-contiguous cells on a worksheet (i.e. the Item column takes up columns C:U, Color V:X, Qty Y:AA, Unit AB:AF, and Extended AG:AK). I would have preferred contiguous columns but the form wouldn't look up to my boss's standards, if that makes sense.

**norie** · Jul 29th 2016

Re: Loop through values in userform to store in array

How exactly is the data organised?

Are you working with merged cells?

By the way you can extract the individual columns from the array using Application.Index.

For example this will put the values from column 2 in the listbox into column C on Sheet1 starting at row 2.

Code

Dim arrData
Dim arr2ndCol


    arrData = ListBox1.List
    
    ReDim Preserve arrData(UBound(arrData), ListBox1.ColumnCount - 1)
    
    arr2ndCol = Application.Index(arrData, , 2)
    
    Sheets("Sheet1").Range("C2").Resize(UBound(arr2ndCol, 1) + 1, UBound(arr2ndCol, 2) + 1).Value = arr2ndCol

Display More

D34THROW · Jul 29th 2016

Re: Loop through values in userform to store in array

Unfortunately, yes, I am working with merged cells since I only recently discovered Center Across Selection (after I designed the form with merged cells). I did discover today, though, that I can write to the range of the merged cell OR to the upper-left cell of the merged cell and get the same result.

Rows 19-39 are line items with the columns as specified in my last post. C19:U19, V19:X19, Y19:AA19, AB19:AF19, and AG19:AK19 are the Item, Color, Qty, Unit $, and Ext $ columns, respectively, and so on through row 39.

I'm not quite understanding how the code you posted works, though.

EDIT: Well, I've got Materials and Labor both switched to a ListBox with appropriate buttons (only the Add of which works at the moment) and warnings if you put in too many line items for the worksheet form. I feel pretty good about this! Next hurdle is populating the form.

**norie** · Jul 29th 2016

Re: Loop through values in userform to store in array

What do you want to know?

By the way, are you going to be sticking with the multiple rows of controls or are you considering the suggestion I made about using a listbox?

If it's the former it's not a problem really though the code required might be a bit more complicated.

D34THROW · Jul 29th 2016

Re: Loop through values in userform to store in array

I'm just not following exactly what the code is doing; kind of lost on the whole thing, actually. I think the ListBox is populating a 1-dimensional array which is in turn populating the Item column?

D34THROW · Aug 1st 2016

Re: Loop through values in userform to store in array

Happy Monday...just don't want to be forgotten about because of the weekend! I'm close to a solution and not finding much help on Google.

Loop through values in userform to store in array

Participate now!

Share

Users Viewing This Thread