Posts by D34THROW

    We do quarterly inventory at my work, and part of my inventory tasks is data entry - 500+ individual tags entered into a giant spreadsheet. My old way was Find by item number, then the count and tag numbers in the requisite cells. This inventory, I've made a UserForm to do most of it for me; it takes the data I give it (item, count, tag number), finds the item number, and enters the count and tag number in the next empty cells. This works flawlessly, but my issue is this:

    The UserForm closes every time I use it. I would like to keep the UserForm open, dump the data into the spreadsheet, clear it, and keep the userform open. I can't seem to keep it open no matter what I do. Unload Me and then ufInventoryEntry.Show does not work.

    UserForm Code


    Private Sub UserForm_Initialize()
        ' Clear textboxes
        tbItemNum.Value = ""
        tbCount.Value = ""
        tbTagNum.Value = ""
        ' Return cursor to item number
    End Sub

    I'm sure I'm missing something obvious, or not using the right search string in Google. Any help would be MOST appreciated!

    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.

    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.

    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.

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


    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?

    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.

    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!

    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?

    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).

    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.