Auto incremental purchase order numbering in Excel 2003

    I am trying to create a new purchase order system, whereby, I fill in all relevant information, ie, the supplier name, the items ordered, etc. But I would like the purchase order number to auto increment upon opening a NEW form, not when opening ones I've already created. Also, I wouldn't mind if I could have the date automatically come up on new forms. I have tried several VB threads in my document for the po number auto incrementing, but I always get error messages and when I go to open a new one to see if it's worked, I get asked if I want to debug, when I say yes, it doesn't do anything. If anyone could please help me out. I am new to VB and macros, but I am very computer literate. However, if you can spell out any suggestions as much as possible, I would appreciate it. Thanks in advance to the help. :smile:

    Re: Auto incremental purchase order numbering in Excel 2003


    scratch my previous response. I thought I had it sorted, but the VBA string I put into my spreadsheet is actually auto incrementing any PO that I open, including the ones I've already done? Not sure why this is happening and if it can be stopped, but if anyone knows how to get the PO number to auto increment on new PO's only, that would be helpful, as finding a VBA string that actually works is quite difficult for me.
    Thanks.
    PS: this is the VBA string that I've been using:
    Private Sub Workbook_Open()
    Sheets("Sales Order").Range("G4").Value = Sheets("Sales Order").Range("G4").Value + 1
    End Sub

    Re: Auto incremental purchase order numbering in Excel 2003


    I'm not sure I understand what you're trying to do - do you have an example you can post?


    Also, you should use code tags when posting code.

    Re: Auto incremental purchase order numbering in Excel 2003


    I have a PO number in a worksheet I've called Sales Order. The PO number is in cell G4, and what I want it to do is go to the next number when I open a new Sales Order worksheet, not when I re-open one I've already created and saved as a different name. So currently, my spreadsheet is titled Purchase Order and when I save one that I've just done I name it 7020 Triggs Manufacturing, so that it shows the PO number and who the supplier was. Does that make any sense?
    I'm not sure what code tags are. I'm new to VBA and haven't worked with macros at all, but I'm very computer literate with everything else pretty much.

    Re: Auto incremental purchase order numbering in Excel 2003


    Would the following code work?

    Code
    Private Sub Workbook_Open()
If ActiveWorkbook.Name = "Sales Order" Then
Sheets("Sales Order").Range("G4").Value = Sheets("Sales Order").Range("G4").Value + 1
End If
End Sub

    Re: Auto incremental purchase order numbering in Excel 2003


    is that like HTML code? I've worked a bit with that previously and it looks similar. Forgive my ignorance, but why would the color change my problem of auto incrementing on my new spreadsheet rather than auto incrementing on previously saved ones?

    Re: Auto incremental purchase order numbering in Excel 2003


    Sorry - please look at my latest edited post - I was a little hasty when copying code from elsewhere.

    Re: Auto incremental purchase order numbering in Excel 2003


    Would the active workbook name be the name of my entire workbook or what I've named the spreadsheet, because I've named them two different things.


    VB:


    Private Sub Workbook_Open() If ActiveWorkbook.Name = "Sales Order" Then Sheets("Sales Order").Range("G4").Value = Sheets("Sales Order").Range("G4").Value + 1 End If End Sub thank you

    Re: Auto incremental purchase order numbering in Excel 2003


    Sorry, I have tried your VBA code, but it didn't work, didn't change my PO number, but I did change the active workbook name to Purchase Order form, which is what I've titled the workbook, but the spreadsheet itself is titled Sales Order (just to make things confusing I guess)

    Re: Auto incremental purchase order numbering in Excel 2003


    Why don't you upload the xls - I'm having difficulty following this.

    Re: Auto incremental purchase order numbering in Excel 2003


    I tried your code just as you had it and it didn't work that way either, just wanted to check I wasn't being thick changing the name of the active workbook. But I saved the code and saved the workbook as test. then opened a new purchase order form and the PO number stayed the same??

    Re: Auto incremental purchase order numbering in Excel 2003


    Again, I would suggest uploading the workbook.


    The code I posted assumed the following:
    You have an Excel workbook called Sales Order with a worksheet within it called Sales Order as well. When you open this workbook you want the value in Cell G4 on worksheet Sales Order to go up by one. You will then save this workbook as something else. When you re-open this workbook the value in Cell G4 shouldn't change. Am I correct?

    Re: Auto incremental purchase order numbering in Excel 2003


    The workbook is called Purchase Order Form not Sales Order, also need to save the "template" - try the following code:


    Code
    Private Sub Workbook_Open()
    If ActiveWorkbook.Name = "Purchase Order Form.xls" Then
        Sheets("Sales Order").Range("G4").Value = Sheets("Sales Order").Range("G4").Value + 1
        ActiveWorkbook.Save
    End If
End Sub

    Re: Auto incremental purchase order numbering in Excel 2003


    Hi Emmy,
    I had mentioned earlier that the workbook is called Purchase Order form. Anyway, the new VB you've suggested also doesn't want to change my PO number upon opening the Purchse Order form template. Am I doing something wrong, or not doing something I should be? DOH! :crying: this is frustrating for me, but probably is for you as well. sorry.

    Re: Auto incremental purchase order numbering in Excel 2003


    Change the capital F in Form to lower case f, save and closeworkbook and try again.

    Re: Auto incremental purchase order numbering in Excel 2003


    I have tried that and it works fine for incrementing the number for the new PO, but it is also incrementing the number in any of the old, previously saved ones. Is there anything I can add to the VB to make it only increment on the new PO?

    Re: Auto incremental purchase order numbering in Excel 2003


    I'm not sure why you're having problems - are you changing the name of the workbook when you are saving?
    This is how it should work:
    Open Purchase Order form workbook (PO number goes up by one)
    Save the workbook as something else e.g. Purchase Order 1
    When you open Purcharse Order form workbook again PO number will go up by one again
    and when you open Purchase Order 1 PO number shouldn't change.

    Re: Auto incremental purchase order numbering in Excel 2003


    I am opening the Purchase Order form, I fill in what I need to, then save it as 7014 Triggs Metals (example only) then when I go back into Purchase Order form, the number increments as it should. But if I go back into 7014 Triggs Metals, the number also increments...not sure why though? AH, I've just noticed that the PO number increments everytime I open the Purchase Order form, even if I don't save it under another name. Don't really want that to happen either. Rather than having the PO number increment upon opening, would it be better to increment upon saving or closing or something? The below is what I have copied from your earlier post for the updated VB code:


    Code
    Private Sub Workbook_Open()
    If ActiveWorkbook.Name = "Purchase Order form.xls" Then
        Sheets("Sales Order").Range("G4").Value = Sheets("Sales Order").Range("G4").Value + 1
        ActiveWorkbook.Save
    End If
End Sub



    is this not correct?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login