Posts by CJamesUK

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    Morning All,


    So im copying a sheet full of formulas and i want to save the text rather that the formulas mainly due to the fact one of the formulas will delete all the text with a button click.

    This is the code im using to create a new sheet just need to know what coding would be the best to add to do this.


    Thanks in advance


    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

    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

    IF(Order!C4=0,"",(Order!C4))

    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