Auto incremental purchase order numbering in Excel 2003

Re: Auto incremental purchase order numbering in Excel 2003

Quote from rackham;581876

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:

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?

Display More

If you are renaming the workbook after you have created your PO from the template, the code you have above shouldn't be executing as it checks for the workbook name on the first line of code....

I'm not following this.

Re: Auto incremental purchase order numbering in Excel 2003

I'm sorry I don't know what to suggest. It works fine for me when I save the workbook as something else - PO number stays static.

Also, since there is no BeforeSaveAs event on Excel 2003 I see no way of stopping the problem you encounter when opening the main workbook but don't create a sub workbook as it were.

I'm out of ideas and give up - sorry I couldn't help.

Re: Auto incremental purchase order numbering in Excel 2003

rackham, please use code tags when posting code, thanks. [noparse]

YOUR CODE HERE

[/noparse]

Re: Auto incremental purchase order numbering in Excel 2003

I'm sorry I don't know what to suggest. It works fine for me when I save the workbook as something else - PO number stays static.

Also, since there is no BeforeSaveAs event on Excel 2003 I see no way of stopping the problem you encounter when opening the main workbook but don't create a sub workbook as it were.

I'm out of ideas and give up - sorry I couldn't help.

Re: Auto incremental purchase order numbering in Excel 2003

Apologies for the double post - I'm having internet/posting problems.