Auto Generate Invoice Number - Recycle number if document isn't saved

[jpabz]

Hello,

Is there a way to generate sequential invoice number and recycle the assigned number it if the user does not save the document?

This is the code:


Sub AutoNew()

Order = System.PrivateProfileString("O:\personnel\Operations\data\Common\PO TEST\Settings.Txt", _
"MacroSettings", "Order")

If Order = "" Then
Order = 1
Else
Order = Order + 1
End If

System.PrivateProfileString("O:\personnel\Operations\data\Common\PO TEST\Settings.txt", "MacroSettings", _
"Order") = Order

ActiveDocument.Bookmarks("Order").Range.InsertBefore Format(Order, "0000")

ActiveDocument.SaveAs FileName:="O:\personnel\Operations\data\Common\PO TEST\REF number\Archive\ QUOTE-FOR-SERVICES - " & Format(Order, "0000") & " - " & Format(Date, "dd-mmm-yyyy")

End Sub

Thank you

[SamT]

Save as before you Save Order". Test if SaveAs was Canceled then Save or not save "Order"

[jpabz]

I am sorry but I do not get what you just said. Sorry, I am new to VBA.

[jpabz]

Can anyone help with this? Much appreciated!

[gmayor]

What he meant was that you should only update the settings text file if the document has been saved, but as the macro saves the document automatically with a derived name, it will always be saved. You would have to provide a prompt to save if you also want to provide the option to cancel. Under what circumstances wouldn't the document be saved?

FWIW I would insert the number IN the bookmark and not next to it, you can then update the bookmark to reflect a correction e.g.


Option Explicit
Sub AutoNew()
Dim Order As String
On Error Resume Next
Order = System.PrivateProfileString("O:\personnel\Operations\data\Common\PO TEST\Settings.Txt", _
"MacroSettings", "Order")
If Order = "" Then
Order = 1
Else
Order = Order + 1
End If
FillBM "Order", Format(Order, "0000")
ActiveDocument.SaveAs Filename:="O:\personnel\Operations\data\Common\PO TEST\REF number\Archive\QUOTE-FOR-SERVICES - " & Format(Order, "0000") & " - " & Format(Date, "dd-mmm-yyyy")
System.PrivateProfileString("O:\personnel\Operations\data\Common\PO TEST\Settings.txt", "MacroSettings", _
"Order") = Order
lbl_Exit:
Exit Sub
End Sub

Private Sub FillBM(strBMName As String, strValue As String)
'Graham Mayor
Dim oRng As Range
With ActiveDocument
On Error GoTo lbl_Exit
Set oRng = .Bookmarks(strBMName).Range
oRng.Text = strValue
oRng.Bookmarks.Add strBMName
End With
lbl_Exit:
Set oRng = Nothing
Exit Sub
End Sub

[jpabz]

Hi Graham,

Thanks for the reply.

The time it wouldn't be saved is if the user decides not to complete the form...

What do you mean insert the number IN the bookmark?

Thanks!

[gmaxey]

Compare your code to Graham's example. You insert your order number text before the bookmark range and Graham calls a functions to wrap the bookmark range around the order number text

[jpabz]

Figured it out!

Thank you Graham and Greg!