Pulling Multiple Rows of Data into a Userform

[gxm100]

Hi Everyone,

In the attached workbook there is a button for "Create Manifest" and a button for "Edit Manifest". After clicking 'create manifest' you can enter info about an order, and upon clicking submit, it writes it to the 'Manifests' worksheet.

What I am looking for

I would like for the functionality to then be to be able to click the 'Edit Manifest' button, and to select the reference IDs which may be located on the 'Manifests' worksheet from the combobox, and upon that combobox change, for the text boxes to populate with the information on that corresponding order so that it may be changed.

Manifests will not always be three lines, and eventually that 'Manifests' worksheet could become hundreds or thousands of rows long.

I am at a loss for how to accomplish this.

[BadlySpelledBuoy]

When you say the Manifests worksheet will contain thousands of rows of data, am I correct in assuming that would cover multiple Reference IDs?
If so then is there a maximum number of rows of data that any given Reference ID would cover?

Reason I ask is I think you may struggle with this approach (combo and text boxes for detail) if there could be many many rows per ID as you'd need a very long form and/or need to make it dynamic, which can be a pain in the posterior, and may even run into an upper limit of userform capability.

Perhaps a slightly different approach...? A simple userform with a combobox to select the Reference ID and a listbox under that which would detail each relevant row of data. The you can scroll through the list if it's too long to view entirely, double click on an item you wish to change and the specific detail of that item would show in a separate userform for updating/amending before taking you back to the listbox form.

BSB

[gxm100]

Hi BSB -

Your assumption is correct, there will be many reference IDs and one reference ID can cover many rows (generally not more than 10 rows, but that isn't a guarantee unfortunately).

That's a good idea, and not one that I had considered! Certainly would seem more functional. I'll give that a go. Would you have any recommendation on how to handle it if I wanted to delete one of the rows and then write it back to the 'Manifests' worksheet?

Thank you very much for your the suggestion!

gxm

[BadlySpelledBuoy]

Hi gxm,

No problem at all. I build many tools like this for my day job so have had to develop different solutions for many different scenarios. Your need here is very similar to something I worked on recently which is why that idea sprang to mind.

I'm not sure what you mean by "if I wanted to delete on of the rows and then write it back to the 'Manifests' worksheet".

Do you mean in terms of updating a row of data or deleting it and replacing it with an entirely new one?

BSB

[gxm100]

BSB - I guess I should actually back up and make sure that I can get this to work first!

I'm struggling with how I can make the combobox populate the listbox. I suppose that's why I didn't think of it, because I've never done it haha. I generally make combo box selections populate text boxes because it is what I know how to do by essentially telling each textbox to equal one value in an array.

I'm not really sure where to begin with the listbox. Have you got any suggestion on how to make the work or possibly know of another thread where the topic may be covered?

Thank you again for the help.

[BadlySpelledBuoy]

I'm happy to put a demonstration version together for you. It will take me a little while to do (in the middle of a couple of other things right now too) but I'll post back ASAP. It won't necessarily be pretty but with any luck it'll be functional!

I'll write the code (hopefully) in a simple way that's easy to follow and will add comments to explain it all.

I just need to know is the sample data in your attachment actually representative of the sort of thing you'll be recording? i.e. your sample data doesn't show text in fields that would be purely numbers in reality, or vice versa? And are there any static lists for any of the fields? If so, which fields and what are the lists?

BSB

[gxm100]

That would be more help than I could ask for!

Yes, the information in the Manifests sheet is representative of reality. And no, presently I do not have any static lists for the fields.

Much appreciated!

Best regards,

gxm

[BadlySpelledBuoy]

I'm happy to help. Leave it with me and keep an eye on your forum notifications for when I post back.

Just one other question, the comboboxes for 'Product' and 'Quality', if you don't have any static lists, what are you populating those with? Do they need to be comboboxes or could they just be (freetext) textboxes?

BSB

[gxm100]

Hey BSB - The idea initially was to have them populated with static lists, however, I changed my mind on that. So certainly no reason they can't be free text boxes.

[BadlySpelledBuoy]

Apologies for the delay, as I mentioned I was in the middle of a couple of other things at the time.

OK, this is a very rough and ready example.

I've added a "Control" sheet with two self explanatory buttons.

Click the first to add new manifest details. A form will open with all the usual detail to be completed. Then click the "Add to manifest" button and the data will be stamped to the Manifests sheet.
The form will then clear, except for the Reference ID field, ready for adding another manifest item for the same ref number.
When all added, click the "Finished" button to close the form.

Click the second to open another form. All the previously recorded Reference IDs will be available in the combobox and when you select one (or type it in) the listbox will populate with all rows pertaining to that reference.
Double click on the record you wish to amend and it will be recalled to a third form (that looks very similar to the first one). You can then change the details and click the "Update Manifest" button to overwrite that record on the Manifests sheet.
There are some limitations to this given your current data structure but I've included full details of that in a notes section in the amend form module.

Comments added to the code so you can, hopefully, follow it without too much trouble, but feel free to ask any questions.

Let me know what you think!!

BSB

[gxm100]

Wow this is incredible! Thank you so much. I'm going to play around with it and read through it, but it looks like it'll do!

Thank you again for taking the time out of your day!

Best,

gxm

[BadlySpelledBuoy]

No problem at all. Happy to help.

Much of it I took from something I'd already built and just amended it to fit this project.

Happy to answer any follow-up questions you have.

BSB

[gxm100]

Hey BSB - this is a few weeks on now but I do have a question on this. First off, thank you so much for the help as this has really been working out for me.

My question is if you would have an idea about how I could control where in the spreadsheet the new Manifest Line gets added. Right now this is looking at a combination of Reference ID and Cases as a unique identifier. What if I knew that I wanted to add a new Manifest line above/under a specific row in tblManifests? Would you have any idea for what I could add/change to make that possible?

No worries if you've not got the time! You've already done plenty for me.

Thanks!

[BadlySpelledBuoy]

Hi gxm,

Apologies for the delay. Been away from the forum for a couple of days.

Inserting at a certain point in the table is certainly possible by changing the below line to include the red part.

Please Login or Register  to view this content.

The number in parenthesis will determine the row number below the table header that a new line will be inserted. 1 would be directly below the headers, 10 would be ten rows below etc.

How do you determine which row to insert at? Are you looking to group Reference IDs or similar?

If so it would be possible to find the last instance of a Ref ID and insert a row after that. Or may be just as simple to add a new row to the bottom of the table then sort the data to group things.

Let me know how you decide where to insert and I'll amend the VBA.

BSB

[gxm100]

Hi BSB! No worries at all - glad to hear back from you.

Yes, what I am looking to do is to insert a row below a specific combination of Ref ID and Product. I am looking to approach it this way because for each Ref ID there may be multiple different products, and if I add a line, I would like for all of the like products to remain with each other.

So essentially I'd like for the macro to look for the first or last line where specific combo of Ref ID + Product exists and then add the new row below that.

Let me know if I need to clarify further - not always the best at explaining requirements hahah!

Best,

gxm

[BadlySpelledBuoy]

Try this version. It will look for the Reference ID in column A and the Product in column C, searching from the bottom row upward.
When it finds a match on both it will insert a new row underneath that row and add the details from the form there.

I hope that's what you meant.

BSB

[gxm100]

A savior as always. That's exactly it. Thanks a million times!

[BadlySpelledBuoy]

Happy to help

BSB

[claireexcel]

Try this version. It will look for the Reference ID in column A and the Product in column C, searching from the bottom row upward.
When it finds a match on both it will insert a new row underneath that row and add the details from the form there.

I hope that's what you meant.

BSB

Hello everyone, hello Badlyspellebuoy. I found this thread and your vba solution to this specific userform after a long search on the internet.
I want to tell you your work and userform are very much apreciated.
I tried to tailor it to my nedds, and it works fine, except for the inserting row under the matching ID and product (found in columns A and C)
It inserts new row with the matching Id and product at the bottom of the table...and NOT under the existing rows with matching ID.
I am using the add manifest command button in the control sheet btw.
Could you please, kindly ndicate what do I have to edit in the vba in order for the userform to add the new row under the last one with matching ID and product?
Thank you so much in advance for any help you are willing to give.

[BadlySpelledBuoy]

I suspect that would be happening because you have blank rows at the bottom of the table, so rather than tag the new line at the end of the data it tags it to the end of the table.
There is rarely a need to have blank rows at the end of a table as formatting and formulas etc. will get copied down as you add new data.

Delete the blank rows and it will work just fine.

If that's not the case, start a new thread with a copy of your file including any amendments to the code.
Send me a PM when you've done that in case I miss it and I'll take a look.

BSB

[FDibbins]

Hello everyone, hello Badlyspellebuoy. I found this thread and your vba solution to this specific userform after a long search on the internet.
I want to tell you your work and userform are very much apreciated......

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread