Macro to store and generate invoice nos in a form

[NSNR]

Hi

I have created a Invoice using a form template in MS word. In this the
format and the text remain fixed as it is password protected.
Only blank spaces are left to be filled by the user while preparing the
Invoice.

Among the blank spaces(fields), there is a space for entering Invoice No. I
wish the system to generate this no. starting as 1 the first time I use it.
Next time I copy the template to prepare a new Invoice, the system should
enter the invoice no. as 2 and likeforth.

Can anybody help me to automate this using macro/any other means.

Regards
NSNRao

 

[Greg]

There are several ways to do this. Here is one that uses an autotext entry
in the invoice template to store the last used number:

Option Explicit
Dim oATE As AutoTextEntries
Dim oTemplate As Template
Dim oString As String
Dim BMRange As Word.Range
Dim oBMs As Bookmarks
Const oFormat As String = "0000"
Sub AutoTextDataStore()
Set oATE = ActiveDocument.AttachedTemplate.AutoTextEntries
Set oBMs = ActiveDocument.Bookmarks
oString = oBMs("SeqNum").Range.Text
On Error GoTo Handler:
Set BMRange = oBMs("SeqNum").Range
BMRange.Text = oATE("dataStore").Value
'Re-insert the bookmark
oBMs.Add "SeqNum", BMRange
oATE("dataStore").Value = _
Format(CStr(Val(oATE("dataStore").Value) + 1), oFormat)
ActiveDocument.AttachedTemplate.Save
Exit Sub
Handler:
oATE.Add Name:="dataStore", Range:=oBMs("SeqNum").Range
ActiveDocument.AttachedTemplate.Save
Resume
End Sub
Sub InvoicerSetup()
Dim oRng As Range
Set oBMs = ActiveDocument.Bookmarks
Set oATE = ActiveDocument.AttachedTemplate.AutoTextEntries
If MsgBox("Do you want to define/redefine the SeqNum" _
& " bookmark at the current insertion point.", _
vbYesNo, "Define bookmark") = vbYes Then
'If already exists then delete it
If oBMs.Exists("SeqNum") Then
oBMs("SeqNum").Range.Delete
End If
'Define and setup bookmark
Selection.Collapse wdCollapseStart
Set oRng = Selection.Range
oRng.Text = Format(InputBox("Enter the starting sequence number: ",
"Set\Reset Starting Number", "0001"), oFormat)
oRng.Bookmarks.Add "SeqNum", oRng
oATE("dataStore").Value = oRng.Text
Else
'Force setup of bookmark
If Not oBMs.Exists("SeqNum") Then
MsgBox "Place the insertion point where you want the" _
& " SeqNum bookmark and start this procedure again."
Exit Sub
End If
End If
If MsgBox("Do you want to set/reset the sequence starting number?", _
vbYesNo, "Starting Number") = vbYes Then
Set oRng = oBMs("SeqNum").Range
oRng.Text = Format(InputBox("Enter the starting sequence number: ", _
"Set\Reset Starting Number", "0001"), oFormat)
oRng.Bookmarks.Add "SeqNum", oRng
oATE("dataStore").Value = oRng.Text
End If
End Sub
Sub AutoNew()
AutoTextDataStore
End Sub
Sub AutoClose()
If ActiveDocument.Name Like "Document#*" Then
If MsgBox("This invoice has not been saved. Do you want to save" _
& " this invoice before closing?", vbYesNo, "Unsaved Invoice") = vbYes
Then
Application.Dialogs(wdDialogFileSaveAs).Show
Else
oATE("dataStore").Value = Format(CStr(Val(oATE("dataStore").Value) -
1), oFormat)
MsgBox "The current invoice number will be recycled."
ActiveDocument.Saved = True
ActiveDocument.AttachedTemplate.Save
End If
End If
End Sub

Fellow MVP Graham Mayor has put together a couple of other methods here:

http://www.gmayor.com/automatic_numbering_documents.htm

 

[NSNR]

Pl let me know where and how this VB script to be used/entered in the MS word
form. Also once I store a number, the invoices will start with this no and
proceed further with the next nos. automatically.

NSNRao