Hi everyone. This is my first post and I hope someone can help me.



I want to create a macro in my Word document that automatically prints Serial No’s when I type them into input boxes. The Serial No. needs to have the format yyyy/0000 where yyyy represents the year and the four zeros a four digit number, for example 2011/0001 or 2011/0010 or 2012/0100. I have obtained the following code and tried to alter it but I’m having problems getting it to do what I want it to. Is there any way it can be modified to give me what I’m after? Here is the code:

Sub macSSerialNo()

' macSSerialNo Macro
' The variables dimensioned below are used in both the Input Boxes to capture the copy numbers
' and the page numbers to be printed

Dim strLMessage, strHMessage, strTitle As String
Dim intDefault, intLowNum, intHighNum, intSerialNum As Integer

'The following lines set up variables used in the display of input boxes

strTitle = "Print multiple Serial numbers"

strLMessage = "Enter LOW Serial number"

strHMessage = "Enter HIGH Serial number"

'The following lines reset variables

intDefault = 0

intLowNum = 0
intHighNum = 0
intSerialNum = 0

'The following lines capture inputs from input boxes

intLowNum = InputBox(strLMessage, strTitle)
If intLowNum = "" Then
GoTo WayOut
End If

intHighNum = InputBox(strHMessage, strTitle)
If intHighNum = "" Then
GoTo WayOut
End If

intSerialNum = intLowNum

'The following DO statement is the beginning of the LOOP that increments the inSerialNum variable

Do

'This block finds "Serial No:, moves however many spaces to the right and then deletes existing Serial 'No

Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "Serial No. : 2011/"
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute
Selection.MoveRight Unit:=wdCharacter, Count:=3
Selection.Delete Unit:=wdCharacter, Count:=2

Selection.TypeText Text:=intSerialNum 'Prints on screen new Serial No
intSerialNum = intSerialNum + 1 'Increment Serial number by 1


Application.PrintOut FileName:="", Range:=wdPrintAllDocument, Item:= _
wdPrintDocumentContent, Copies:=1, Pages:="", PageType:=wdPrintAllPages, _
Collate:=False, Background:=True, PrintToFile:=False

'Once the SerialNum gets too high, the LOOP UNTIL redirects programme follow onto
'End Sub (End of Macro)

Loop Until intSerialNum > intHighNum

WayOut:
End Sub

There are two reasons why the macro isn’t working the way I want it to. The first reason is because:

I cannot get it to work without having the year somewhere in the document and also in this part of the code:

With Selection.Find
.Text = "Serial No. : 2011/"

Is there any way I could use some sort of wild card here instead of having to have 2011 in the document and changing it to say 2012 when I require a year change?

The second reason is to do with getting 0001, 0010, 0100 and 1000 without messing around with the code. I keep having to edit the code as explained below.

In order to get 0001 I am using the following code:

Selection.MoveRight Unit:=wdCharacter, Count:=4
Selection.Delete Unit:=wdCharacter, Count:=2

The document has to have 0000 in it before I type the serial number into the input box so that the last zero becomes a 1

In order to get 0010 I am using the following code:

Selection.MoveRight Unit:=wdCharacter, Count:=3
Selection.Delete Unit:=wdCharacter, Count:=2

The document has to have 0000 in it before I type the serial number into the input box so that the last two zeros become 10

In order to get 0100 I am using the following code:

Selection.MoveRight Unit:=wdCharacter, Count:=2
Selection.Delete Unit:=wdCharacter, Count:=2

The document has to have 00 in it before I type the serial number into the input box. The code adds on the last two digits to become 0100

In order to get 1000 I am using the following code:

Selection.MoveRight Unit:=wdCharacter, Count:=1
Selection.Delete Unit:=wdCharacter, Count:=2

The document has to have a 0 in it before I type the serial number into the input box. The 0 gets replaced by whatever digit I enter followed by the other digits I enter so I do end up with a four digit serial number.

So that’s it. Basically I want a macro that will give me 2011/0001 or 2011/0010 etc etc. Any help with this would be very much appreciated

Hi pikerman,

See attached.

Attached Files

Certificate Demo.zip (20.3 KB, 36 views)

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Hi macropod

Many thanks for your help with my problem. I have run the demo a few times and get the numbers exactly like I want them. I have noticed though that if I only want to print one serial number, it prints it twice and also the next time I run the demo, it prints the last page to be printed again. Perhaps I’m doing something wrong.

Also, I notice that the demo is a Word Macro Enabled Document. I have already typed up the documents I want the serial numbers to appear in as ordinary Word documents. Can these still be used without changing them to macro enabled? The documents contain the words “Serial No.” so how do I get the 2011/0001 to appear after it? Once again, any help on this would be appreciated.

Hi pikerman,

To fix the duplicate printing, I've changed the line:
ActiveDocument.PrintOut
to:
If i > iStart Then ActiveDocument.PrintOut

I'm not sure what you mean by "the next time I run the demo, it prints the last page to be printed again". In my testing, it was the first certificate in the sequence that was duplicated. The above change fixes that.

As for the document being macro-enabled (docm format), that's a Word 2007 thing. Since you're using Word 2003, you can use the .doc format. However, the document still contains a macro and you'll still get the 'enable macros' prompt when you open it.

See attached.

Attached Files

Certificate Demo.zip (14.4 KB, 23 views)

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]