Creating an Auto-Fill Order Form

[allongsound]

Good Afternoon,



I am looking to create an Auto-Filling Purchase Order document in Excel.

We produce machines with various base models, and addons, so we were looking to create a form where you can enter the quantity of machines required, select your base model, tick the addons you want and that will automatically populate the order form with the prices, descriptions and part numbers taken from the product listing tab.

I've attached a example I have butchered from Excel Exposure's VBA Walkthrough Tutorial.

It no longer works, due to the changes I have made to the form, but this is the sort of thing we are looking for. Any help or advice would be greatly welcomed.

Note that this example is based upon our smallest model, we do have products with around a dozen base options and many, many more addons to choose from, so this will need to be expandable. (Possibly up to 50 lines...)

[torachan]

I have had a quick look, all that was stopping it running was four labels had been deleted.
I have put them back on the form as L1, L2, L3 & L4 - you should see them at two locations in the code.
The only other thing I have altered is the range size in the 'name manager' which loads the listbox - as well as the items it was loading a million+ blank rows.
I would not alter anything else as the approach is not one I would adopt - there is too much maintenace involved with your setup.
Revert to tables that self expand/contract as additions/deletions take place.
Also get away from using the 'rowsource' method of filling the listbox and use an array from a table databody to fill the listbox.
any queries get back on this site.
torachan.

[allongsound]

Thanks for the help.

Is it possible for me to set it up the way I was trying to, with the base machine, selecting the additional items and then adding them all in one go?

i.e. a selection box at the top with "Machine 1", "Machine 2", "Machine 3", ... "Machine N" and then options below on check boxes? Click the add to PO button and it all populates in the order form?

[torachan]

Not sure whether it a sensible way or not.
The attached is restrictive i.e.,you have to be disciplined to enter your data in blocks of five, as per existing.
If this is acceptable then the listbox is self maintaining and expands as you add data and the code should also cope with reading in from the data table.
Note, the listbox no longer uses the rowsource, it is loaded by the UserForm_Initialize event.
torachan.

[allongsound]

Hi, Thanks for that. I've had a look at it again and come up with the attached. We have Technical specifications that the customer would usually use to communicate their requirements. So I've decided to utilise these for the ordering.

It still needs refinement though, so any help would be greatly appreciated.

Al

[torachan]

Hi,
The approach is very fragmented, i.e. bits of data been gathered from disjointed locations.
Your data needs to be more organised and singular method of input/output - use UserForm for all data inputs - do not have random data collection on your form template.
Attached is method where all input is entered/edited via UserForm this contains all the data management code - no formula in sheets.
It is far more complex than your initial requirement but demonstrates the approach I would use.
torachan.

[allongsound]

Thank you very much Torachan, I'll have a look over and see what I can learn from it.