Posts by CJamesUK

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

Dim wb As Workbook: Set wb = ThisWorkbook
    Dim ws As Worksheet: Set ws = wb.Sheets("Order Mark Off Template")
    Dim newws As Worksheet, sh As Worksheet, newname
    Dim query As Long, xst As Boolean, info As String

retry:
    xst = False
    newname = Application.InputBox("Enter Works Order Number.", info, , , , , , 2)
    If newname = "False" Then Exit Sub
    For Each sh In wb.Sheets
        If sh.Name = newname Then
            xst = True: Exit For
        End If
    Next
    If Len(newname) = 0 Or xst = True Then
        info = "Sheet name is invalid. Please retry."
        GoTo retry
    End If
    ws.Copy After:=ws: Set newws = ActiveSheet: newws.Name = newname

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 =??

Quote from royUK

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

Quote from royUK

I've edited the code for PickForm so that when a product type is selected then that range of products is loaded to the ListBox

Ty

Quote from royUK

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.

Quote from royUK

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

Quote from royUK

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.

Quote from royUK

That's what we're here for.

I know Roy but I hate to keep asking for help

Quote from royUK

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.

Display More

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

Quote from royUK

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.

Quote from royUK

There's no formulas in that tab in my example

Of course I started to play with this after. this is the code for getting a blank cell instead of zeros

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

Quote from royUK

Which sheet has the formula?

Purchase order tab

Quote from royUK

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?

Quote from royUK

I'll take a look for you as soon as I can. Attach your updated workbook

Thanks Roy your a legend

Quote from royUK

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