Dont worry about it Roy thank you for all your help.
Good Morning Roy,
Correct on the sheet i last uploaded this all works fine with just a few little issue's that will need sorting ie. showing zero's on the print out etc but this is not stopping me from creating a form and getting my ironmongery ordered.
Somewhere we lost our way, This is the current forms im using there are a few little niggles that i need to get sorted but the main thing i cant work out is when using the pickform i add an item with an amout which is FROM STOCK i want it to deduct the amount from the sheet to i keep my stock levels corrects.
The last sheet gave you was cut down quite a bit as you suggested that because i have so many sheets it was going to make it hard to do this and we have seemed to have gone right of track from there.
Im hoping im a bit more clear =??
Check this. The users selected items are entered to the second Listbox and the amount calculated. This can then be posted to the Order Sheet.
I have amended the order sheet slightly to make the code more efficient.
Hi Roy, again thanks for your help but it seems we have gone right of track, not to sure there's now a customer pull down which i don't need unless your just trying to show me how to do stuff.
nothing else seems to work now either
Why do you need the pick userform? I would have thought VLOOKUP could populate the order form.
Also, you order forms have numerous unused rows with formulas, this is not a good idea.
Im not the only person that will be using this and was trying to keep away from the normal excel interface and hopefully make it easier to use maybe i have over thought thing?? Excel is not something ive trained in, Im a CAD user and CNC programmer so really only learning excel from what i can research on the internet.
I'll have a look. Thewcode will need re-writing.
Sorry for the late reply my work PC blew up and only just got a replacement installed yesterday.
Have you had a chance to have a look? if its to much trouble ill just carry on with these options
It's not a problem. I'll see what I kind find in my old examples
Roy so ive now changed the sheets, can you have a look at the pick userform I now can workout how to get the combo box to see the products and list them under.
I think the same coding is used elsewhere so hopefully if you can point me in the right direction there I should hopefully be able to get thru the rest.
I'm not really sure what your formula is doing.
I think your whole workbook could do with a re-design.
All the data on one sheet with an extra column for the type of product.
The Form could be made to filter the data for type.
Then your order forms would be easier to fill.
Basically the formula is looking for Y in the stock column of the order sheet and if it finds a Y the Replace with FROM H&S STOCK.
It probably could do with redesigning but ive kinda stumbled myself through this far not to sure if ill know what to do with all the errors its going to cause lol
That formula works fine for me. What's the differencebetween Order and Purchase Order?
Your stock should be on one sheet then you can populate orders and invoices easily by using VLOOKUP based on stock code. All those different sheets for stock items will only make work for you.
So how did you combine it with the other formula? That's my problem
=IF(ISNUMBER(SEARCH("y",Order!G4)),"FROM H&S STOCK",Order!C4)
Ive used different sheets to make it easier for the vba userform so ill just have to find a way to do the stock how it is.
It might be easier to see the workbook. Is it in the one with the UserForm?
I have an Inventory example which I was going to suggest could be incorporated into your workbook
Good Morning Roy
Yes same workbook, I have tried using the show a zero in cells that have no value in excel options but when I use my email macro it still picks up the zeros so was hoping using this formula would work.
Good afternoon all.
I already have a formula in the cell to look for a "y" and replace with "From Stock"
IF(ISNUMBER(SEARCH("y",Order!G4)),"FROM H&S STOCK",Order!C4)
and I now what to add a formulas like this to get the cell if zero make blank
Ive tried to get my head round getting these to work together can anyone help?
I have re-coded ProductFormEdit. The other form is not necessary.
Works lovely Roy thank you, I have made some changes to it and used it to get rid of some of the other userforms.
Last thing I have to try and sort is when I use the pick form and mark that im using items from stock that it removes that amount from my stock amount
Sorry for the late reply, im off work doing DIY.
Ill have a proper look monday
Once again thatnks so much for your help
Thanks, I know what you mean and i need a edit form for suppliers,product type etc etc. i was just going to add and edit form for each userform a bit messy but hey
Thanks Roy tbh ive not got a clue on how to extract what i need out of that sheet