For Loop to Add Items to a Listbox!!!

Sample added, the listboxes will be under the products on the order sheet, if you have more questions just ask I will do my best to explain and help out!!!! Thanks for the help

Use data validation.

1. Select the range you want data validation in (B11:B16)
2. In the ribbon go to Data -> Data Validation -> Data Validation
3. In the box that just popped up under the Allow combobox select "List"
4. Click the "Source" text box. Select the Master List sheet. Select your product range D6:D16. Click Ok

That will not work as the master list can always change and I only want the items that have the identical store number to populate in dripdown list...

I will need this listbox to prepopulate the rest of the information in the prder sheet based on the selection it should bring up prive, location and other stuff from the master list when a product is selected... Will this still work if so how will i reference that?

Sounds like you are looking for a a worksheet change event on the dropdown boxes. Not all of the information on the preorder sheet is in the master list so I have included to code for the code location. You can follow the theme with the main sheet to get the rest of the data.

Please Login or Register  to view this content.

Hmmm im trying to figure this out ill do some research to why it doesnt work and see if i can fix it.

It does work. I tested it on Tinbendr's sheet (which he did a really nice job on for you). Make sure you are putting the code in the proper place under sheet 1 in the VBE

Yea but i cant get it to work on a bigger sheet there is something different in the code I really appreciate the help!!! Thank yall so much!!! Im away from pc now but ill work with it again later again Thanks a ton

Works like a champion!!!! the listbox part that I asked for, if anyone wants to help me make the other information populate based on listbox selection that would be AWESOME!!

Where in the code is it making the other data validation lists for the order form, how do I move them up and down or add more to the order form so I can continually add more products...???

I have attached the big spreadsheet I am working with trying to get all of this working... I need the order form to have listboxes to select the product in the a column starting at A31, I dont know what else to do to get that... I also need it to populate the corresponding data on the order form from the master list based on the selection in COLUMN A...

Thanks in advance

Invoicing Testing.xls

I have that part working, but I need help when I select a product that it populates the row in the order sheet with the data in the master list row for that selected product...

Thanks,

Ok, I believe I have the concept down of what I can do to get around it if nobody can help me figure it out that way... I can use the combo box on change event to clear the filter on the master list, then when a sold-to is selected the data is filtered down to only that sold to... this will eliminate getting the wrong results... this or if there is a way to use vlookup in vba and reference the product name as well as the sold-to number to get the row results within the order sheet....

I have no idea how that works... can you explain how I can add more things for it to post cause I need it to bring everything from the row on the master list to the order sheet for each one except like 2 colunmns... I didnt think it would be this complicated to be honest...

I can say this I do not need a combo box on each order sheet for the sold-to number... those are constant numbers and will be the same for each location so I wont need to change the sold to ever on any order sheet but they WILL be different on each sheet if that makes sense.. .Anton will be the 10004018 number and Mallet will be like a 837281 number... but they will always be the same...

That being said could the listbox selection on the product be a macro of some type that uses a vlookup that pulls that product from the master list with the Sold-to number off of the order sheet and brings all of the data from the sheet and inputs it into each cell on the order sheet... Hopefully this can be done all in a macro or behind the scenes kind of how you did it but without it showing what it will be inputing just mabye select the product and hit post and it does it the first row in the order sheet and then pick a different product and it goes to the next row but adds all of the data I need...

Sorry to keep this going but I really need this to work the right way for me to get the rest of this project completed properly

Invoicing Testing Update.xls

I need the data below to be input into the order sheet... I cannot find where the error is so I have re-attached the new file updated the master list and see if you can help me out, I didnt realize what you did already does what I ask I just need to figure out a way to put it to the side of the sheet instead of a popup as we would prefer not having a popup like that...

Thank you for all the help!!!!

Please Login or Register  to view this content.

It's been a long weekend. (I took the wife car shopping and I'm exhausted. We were successful.)

I hear you, hope that new car did not empty your pocket books!!! haha that is excellent though I really appreciate all the help with this!!

I need to be able to add things to the Master list and the prices need to correspond even when the Master list is not in order... Is this possible? Also, I dont see where you are adding the stuff to the order sheet but it is doing it can you explain where it is doing this?

Invoicing Testing Full Sheet.xls

Ok, I figured out you just put the index formula in each box but when I try to change it even if I change it to something and change it back it gives me N/A... why is that??? this way will work perfectly if I can get the bugs worked out or atleast I think it will... I have attached the master sheet that I am working with mabye you will be able to see a little better what exactly I am going after...

I am hoping to be able to add more things to the master list whenever needed in the future and it will automatically update the listbox and everything... even if I have to make a button for it or something that will be fine.


Thanks,

Ok I got everything working properly but having 2 issues... Where can I change the column that it pulls the vallist from if u can highlight that for me it would be great... Also on the first product it is trimming the first letter so it doesnt locate it if you can tell me how to fix that would be great!!

I still cannot get any other sheet to show the correct product list... All of them show all the products instead of just for that plant...

Sweet!!!

Everything works perfectly now I think, ill check it out after work.

Any chance you can think of a way to copy and paste the orders from the order sheets to a master order sheet... Without copying the blank rows? I tried using count tows in vba but cannot seem to get it to copy only the rows with data.

Thanks,

Invoice Testing 0910.xls

Ok, here is the updated file... Can you look it over and see what I have jacked up to make each sheet not show the proper products? I know they were working before when I transfered all the data but for some reason they all show the same ones now and I am not sure what I did...

As if I need help with that also... YES, PLEASE if you can help me with that.... The data that goes into the master list does not need to correspond with any of the master list or anything it simply just needs to be a copy and paste frmo the order sheet to the master order list but I dont want any blanks or any junk pasting only the data... if that makes sense...

Thanks,

I understand the formulas but the Listboxes in the say Willard sheet are not showing the correct products for Willard plant... It does show the proper ones for Anton and Block 31 but not the Willard ...

O WTH!!!! I swear I ran that piece like 100 times... of course the one time I do it after you say that it worked... FML

Thanks,

Should I start a new thread on the copy/paste piece or will it just get deleted?

Ok, I have the buttons and the copy and paste working but I just did record macro and its only for the Anton one... It does not take into account any of the blank rows or data I just have it copying and pasting all the rows in the order sheet whether I fill them out or not... I really need it to countrows in vba for the copy and paste part.... I would really like your help doing so... I hope it is easy.



Just throwing this out there is this possible???
The master order list sheet I am going to have a custom order # that I put on every order, then when it is returned to me it will have the REAL order # on it... but it will just be the order sheet per plant... Is it possible to make a macro in this file that I can press a button it will locate the Filename of the order sheet find the Custom order # in it compare it to the custom order # column in my worksheet and put the REAL order number wherever the Custom order number is equal from the new sheet to the master orde list???

Please Login or Register  to view this content.

I fail at getting that to work at all, it just pastes a long row in the top row of the master order list sheet...

LRMOL in the loop never counts up in the for loop

I got it figured out!!!

Now, I need the tank number to show up properly on the order sheet. Is there a way to make another valist when I click on a product with multiples? Say I click trac107+ at block31 there are like 6 same ones there, can the tank number be a list of which tank it will be delivered too?

Thanks,

I have been trying this for about 2 hours and cannot get anywhere on the coding part... I tried for loops and everything else... Basically I need another update list that updates when the product is selected or the active sheet changes and uses the selection in the product & plant name to make a listbox for the Tank #.... I will continue to try and figure it out and if I do ill post the answer.

The following is what I have... what I need is someone to help me put an if statement so the list is only created if the selection in the product box is either 90005 or Trac107+...... can someoen help me put this if statement in there in the proper place....

2) Is there a way to put an if statement with the Index array formula that is in the cells? I need the tank # to be if product = trac107 or 90005 to go off of the plant location if not leave blank.... or just put the plant location from the master list....

Thanks,

Please Login or Register  to view this content.

The best way would be if the product selected has multiple locations then make the plant location a dropdown list, if not leave plant location blank but tank number will be the only one available

Well the only place i need a list will be the location, so based on product selection if the product has multiples then the plant location on order sheet will be a list based on plant location column in master list. Then the formula for tank will need to be where location and product are equal pull tank number. If tgere are no multiple products then just put location and tank number as normal.
Hopefully that makes sense

Ok, to explain further here is the logic... If Product DOES NOT have multiples for that plant then Plant Location Column on order sheet = BLANK & Tank # equals the corresponding tank # for that product. IF product DOES have multiples for that plant then Plant Location becomes a List of Locations with that product & Tank # equals the corresponding tank for that location & that product..... hopefully that makes sense.....
So for 1720 at Block 31 I simply select 1720 as a product, plant location stays empty and tank# populats.
For 90005 as a product I select 90005 as a product, plant location becomes a list of locations within that plant that use that product, I select location & Tank # populates accordingly....

If this is way to complicated let me know

Yes whatever is needed to make it better