User to Specify Starting Number for Sequential Numbering

[Code Numpty]

I am using this macro (courtesy of Doug Robbins with help from Jay Freedman)
to sequentially number certificates where data other than the serial numbers
is in ask fields.

The macro is in a template and runs automatically on Document_new. My
problem is that I need the user to specify the starting serial number every
time, without opening the settings.txt. Can this be done with a user form?
Grateful for some help with this, thanks.

Sub SEQserialnumber()
'
' SEQserialnumber Macro
' Macro created 11/09/2007 by Sharon
'
Dim Message As String, Title As String, Default As String, NumCopies As
Long
Dim Rng1 As Range

' Set prompt.
Message = "Enter the number of copies that you want to print"
' Set title.
Title = "Print"
' Set default.
Default = "1"

' Display message, title, and default value.
NumCopies = Val(InputBox(Message, Title, Default))
SerialNumber = System.PrivateProfileString("C:\Settings.Txt", _
"MacroSettings", "SerialNumber")

If SerialNumber = "" Then
SerialNumber = 1
End If

Set Rng1 = ActiveDocument.Bookmarks("SerialNumber").Range
Counter = 0

While Counter < NumCopies
Rng1.Delete
Rng1.Text = SerialNumber
ActiveDocument.PrintOut
SerialNumber = SerialNumber + 1
Counter = Counter + 1
Wend

'Save the next number back to the Settings.txt file ready for the next use.
System.PrivateProfileString("C:\Settings.txt", "MacroSettings", _
"SerialNumber") = SerialNumber

'Recreate the bookmark ready for the next use.
With ActiveDocument.Bookmarks
.Add Name:="SerialNumber", Range:=Rng1
End With
'Next line added to update { REF SerialNumber }
ActiveDocument.Fields.Update

ActiveDocument.Save

End Sub

 

[Shauna Kelly]

Hi

Do you mean:

(a) You no longer want to store the starting number for the next job in the
settings file, but instead want to require the user to provide the starting
number. In this scenario, you don't need the settings file any more.

or

(b) You want the user to be able to accept the starting number stored in the
settings file, but allow the user to over-ride that number with their own
choice. In this scenario, you still need the settings file.

Hope this helps.

Shauna Kelly. Microsoft MVP.
http://www.shaunakelly.com/word

 

[Jay Freedman]

After the NumCopies = .... statement, add something like this:

Message = "Enter the starting serial number"
Title = "Serial Number"
SerialNumber = Val(InputBox(Message, Title, Default))

Then delete the statements that read SerialNumber from Settings.txt
and write the new SerialNumber to Settings.txt.

You really should declare SerialNumber As Long and Counter As Long;
because they aren't declared, they have the default data type of
Variant, which could lead to bugs if you modify the macro later. (See
http://www.word.mvps.org/FAQs/MacrosVBA/MaintainableCode.htm.)

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.

 

[Code Numpty]

Adding
Dim Counter As Long - no problem

Adding
Dim SerialNumber As Long
Results in Type mismatch error with the debugger highlighting
If SerialNumber = "" Then

****************************

Problem number 2, the print fires off before the ask fields come into play.
I'm getting out of my depth here :-(



:

 

[Jay Freedman]

To solve the first problem, delete the lines

You don't need them any more after you change to getting the SerialNumber
from the Val function. (The expression If SerialNumber = "" checks whether
it's an empty string, but now SerialNumber is a number, not a string.)

To solve the second problem, put another ActiveDocument.Fields.Update
statement into the macro where you just removed the three lines. That should
cause the Ask fields to fire their popups. If the Ask fields pop up again at
the end of the macro, you'll need some more code to fix that. (I usually
avoid Ask fields because they're so pesky.)

 

[Code Numpty]

Almost there.
All works a treat except, as you said Jay, the ask fields fire their pop-ups
again at the end.

 

[Jay Freedman]

OK, replace the ActiveDocument.Fields.Update line nearest to the end
of the macro with these lines:

Dim Fld As Field
For Each Fld In ActiveDocument.Fields
If Fld.Type = wdFieldRef Then Fld.Update
Next

This is more selective than just updating all the fields; in
particular, the Ask fields (which have .Type = wdFieldAsk) won't be
updated the second time.

 

[Bryan]

Hey all! I've inserted the macro and have been playing with it along with
the changes. I must preface that I am completely out of my element here. I
am quite proficient with Access but have never worked with Macros in Word.
What I am trying to accomplish in a nutshell is: I have a line on my word
doc
Serial Number ____________
How do I call this/name this for the macro to know where to run it and can
this be automatically run when the doc is opened? And if I am simply
editting the doc and not printing it, can it be canceled?

Thanks in advance,
Bryan

 

[Doug Robbins - Word MVP]

If you name the macro autoopen(), it will run when the document is opened.

However, if you want to be able to open the document without the Serial
Number being updated and only have it updated when the document is printed,
you might be better off putting the code in macros named FilePrint and
FilePrintDefault so that it will run when the document is printed.
--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Bryan]

Thanks Doug, I'll try that. That answers my second question, but I am still
not sure how to insert and call the Serial Number field. I tried calling it
"Serial Number(s)" and inserting a field MacroButton/serialize, (which is the
existing name of the macro.) When I double click on the field in my document
it opens up the dialog, (Message boxes,) but after I enter the number of
copies to print and starting serial number I get an error, "The requested
member of the collection does not exist. As I stated previously, I know zip
about using macros in Word. Here is my code as it now stands:

Sub Serialize()
'
' Serialize Macro
' Macro recorded 01/08/2008 by sna142
Dim Message As String, Title As String, Default As String, NumCopies As Long
Dim Rng1 As Range, SerialNumber As Long, Counter As Long

' Set prompt.
Message = "Enter the number of copies that you want to print"
' Set title.
Title = "Print"
' Set default.
Default = "1"

' Display message, title, and default value.
NumCopies = Val(InputBox(Message, Title, Default))
Message = "Enter the starting serial number"
Title = "Serial Number"
SerialNumber = Val(InputBox(Message, Title, Default))

ActiveDocument.Fields.Update

Set Rng1 = ActiveDocument.Bookmarks("Serial Number(s)").Range
Counter = 0

While Counter < NumCopies
Rng1.Delete
Rng1.Text = SerialNumber
ActiveDocument.PrintOut
SerialNumber = SerialNumber + 1
Counter = Counter + 1
Wend

'Save the next number back to the Settings.txt file ready for the next use.
System.PrivateProfileString("C:\Settings.txt", "MacroSettings", _
"Serial Number(s)") = SerialNumber

'Recreate the bookmark ready for the next use.
With ActiveDocument.Bookmarks
..Add Name:="SerialNumber", Range:=Rng1
End With
'Next line added to update { REF SerialNumber }
Dim Fld As Field
For Each Fld In ActiveDocument.Fields
If Fld.Type = wdFieldRef Then Fld.Update
Next

ActiveDocument.Save

End Sub

 

[Doug Robbins - Word MVP]

See the article "Sequentially numbering multiple copies of single document
using a macro" at:

http://www.word.mvps.org/FAQs/MacrosVBA/NumberCopiesOf1Doc.htm


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Bryan]

Thanks for your help Doug! I copied the code and created the bookmark as
instructed, but I am apparently missing something. According to the macro,
this should run on its own or so it seems. I get nothing at all. I have to
manually run the macro and end up with a type mismatch on the following line:

SerialNumber = System.PrivateProfileString("C:\Settings.Txt", _
"MacroSettings", "SerialNumber")

I am using the code as written with the exception of adding the SerialNumber
As Long, Counter As Long as was in the previous entries. I'll later use the
rest of the modifications suggested as I have serial numbers preassigned and
dozens of docs already written that I'll be adding this to. What I am doing
now is practicing on a blank doc so I can understand how it all works.

Thanks again!

 

[Bryan]

Hey Doug,

I've had some time to work on this and have almost got it, so I hope you
haven't responded to my post from last night before you read this.
Everything seems to be working according to my devious plan with the
exception of two formatting issues. I am bookmarking three items. The PPO
Number works great. It'll be the same on all docs printed in that set.
However the Serial Number has a leading zero, i.e., 08021000, and I cannot
seem to get that to work. I tried
Rng2.Text = Format(ULLabel, "0#") and what is in the code I will include,
but I always end up without the zero. The second problem is the UL Label.
That looks like this:
BR- 123456.
I cannot simply lock in the "BR-" as we use 4 different labels with
different prefixes. We will have to be able to enter it from the message
box.
One other question, (hopefully I haven't reached my quota yet!) Is there
any way I can use a Dialog box with the four questions in it instead of four
message boxes? My research has come up empty!
Again, thanks for all your help!

Sub AutoOpen()
'
' Serialize Macro
' Macro recorded 01/08/2008 by sna142
Dim Message As String, Title As String, Default As String, NumCopies As Long
Dim Rng1 As Range, SerialNumber As Long, Counter As Long, ULLabel As Long,
PPONumber As Long

' Set prompt.
Message = "Enter the number of copies that you want to print"
' Set title.
Title = "Print"
' Set default.
Default = "1"

' Display message, title, and default value for PPO Number.
NumCopies = Val(InputBox(Message, Title, Default))
Message = "Enter the PPO Number"
Title = "PPO Number"
PPONumber = Val(InputBox(Message, Title, Default))

' Display message, title, and default value for Serial Number.
Message = "Enter the starting Serial Number"
Title = "Serial Number"
SerialNumber = Val(InputBox(Message, Title, Default))

' Display message, title, and default value for UL Label.
Message = "Enter the starting UL Label number"
Title = "UL Label"
ULLabel = Val(InputBox(Message, Title, Default))

ActiveDocument.Fields.Update

Set Rng1 = ActiveDocument.Bookmarks("SerialNumber").Range
Counter = 0

Set Rng2 = ActiveDocument.Bookmarks("ULLabel").Range
Counter = 0

Set Rng3 = ActiveDocument.Bookmarks("PPONumber").Range

While Counter < NumCopies
Rng1.Delete
Rng2.Delete
Rng3.Delete
Rng1.Text = Format(SerialNumber, "########")
Rng2.Text = Format(ULLabel, "#########")
Rng3.Text = PPONumber
ActiveDocument.PrintOut
SerialNumber = SerialNumber + 1
ULLabel = ULLabel + 1
Counter = Counter + 1
Wend

'Recreate the bookmark ready for the next use.
With ActiveDocument.Bookmarks
..Add Name:="SerialNumber", Range:=Rng1
..Add Name:="ULLabel", Range:=Rng2
..Add Name:="PPONumber", Range:=Rng3
End With
'Next line added to update { REF SerialNumber }
Dim Fld As Field
For Each Fld In ActiveDocument.Fields
If Fld.Type = wdFieldRef Then Fld.Update
Next

End Sub

 

[Doug Robbins - Word MVP]

If you want the serial number to always appear as an eight digit number with
leading zeros, use Format(ULLabel, "00000000")

I would suggest that you should be using a userform instead of inputboxes.

See the article "How to create a Userform" at:

http://word.mvps.org/FAQs/Userforms/CreateAUserForm.htm

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Bryan]

Last time, I promise!!!!!!!! I have completed the project and added several
more things making the procedures more customer specific in their
requirements. As for the ULLabel format issue, I had to remove "Val" from my
inputbox as it would only let me insert a number. I also added hyperlinks to
auto open associated drawings and docs to get Rev info from to keep us all
from having to chase them down each time we do a test procedure. Now, I can
create 100 sequential test procedures in the time it takes to do one!!!! I
will have to invest time into learning how to create the Input boxes as you
suggested, but for now the message boxes are still light years ahead of what
we were doing. Not too bad for my first Word Macro!!!!!!!!! Thanks for all
your help and the completed code is as follows:
Sub AutoOpen()

' Serialize Macro
' Macro recorded 01/08/2008 by Bryan Daniels
Dim Message As String, Title As String, Default As String, NumCopies As
Long, ULLabelPrefix As String
Dim Rng1 As Range, SerialNumber As Long, Counter As Long, ULLabel As Long,
PPONumber As Long
Dim SchematicRev As String, SpecRev As String, AssyDwgRev As String

' Set prompt.
Message = "Enter the number of copies that you want to print"
'Set title.
Title = "Print"
'Set default.
Default = "1"

' Display message, title, and default value for PPO Number.
NumCopies = Val(InputBox(Message, Title, Default))
Message = "Enter the PPO Number"
Title = "PPO Number"
PPONumber = Val(InputBox(Message, Title, ""))

' Display message, title, and default value for Serial Number.
Message = "Enter the starting Serial Number"
Title = "Serial Number"
SerialNumber = Val(InputBox(Message, Title, ""))

' Display message, title, and default value for UL Label Prefix.
Message = "Enter the UL Label Prefix (2 Letters)"
Title = "UL Label Prefix"
ULLabelPrefix = InputBox(Message, Title, "")

' Display message, title, and default value for UL Label.
Message = "Enter the starting UL Label number (NUMBERS ONLY!)"
Title = "UL Label"
ULLabel = Val(InputBox(Message, Title, ""))

Documents("DPS2 019022355 Test Proc.doc").Hyperlinks(1).Follow

' Display message, title, and default value for Schematic Revision.
Message = "Enter the Schematic Revision"
Title = "Schematic Revision"
SchematicRev = InputBox(Message, Title, "")

Documents("DPS2 019022355 Test Proc.doc").Hyperlinks(2).Follow

' Display message, title, and default value for Assembly Drawing Revision.
Message = "Enter the Assembly Drawing Revision"
Title = "Assembly Drawing Revision"
AssyDwgRev = InputBox(Message, Title, "")

Documents("DPS2 019022355 Test Proc.doc").Hyperlinks(3).Follow

' Display message, title, and default value for Spec Revision.
Message = "Enter the Spec Revision"
Title = "Spec Revision"
SpecRev = InputBox(Message, Title, "")

Documents("DPS2 019022355 Test Proc.doc").Activate

ActiveDocument.Fields.Update

Set Rng1 = ActiveDocument.Bookmarks("SerialNumber").Range
Counter = 0

Set Rng2 = ActiveDocument.Bookmarks("ULLabel").Range
Counter = 0

Set Rng3 = ActiveDocument.Bookmarks("PPONumber").Range

Set Rng4 = ActiveDocument.Bookmarks("ULLabelPrefix").Range

Set Rng5 = ActiveDocument.Bookmarks("SchematicRev").Range

Set Rng6 = ActiveDocument.Bookmarks("SpecRev").Range

Set Rng7 = ActiveDocument.Bookmarks("AssyDwgRev").Range

While Counter < NumCopies
Rng1.Delete
Rng2.Delete
Rng3.Delete
Rng4.Delete
Rng5.Delete
Rng6.Delete
Rng7.Delete
Rng1.Text = Format(SerialNumber, "00000000")
Rng2.Text = Format(ULLabel, "000000")
Rng3.Text = PPONumber
Rng4.Text = Format(ULLabelPrefix, ">")
Rng5.Text = Format(SchematicRev, ">")
Rng6.Text = Format(SpecRev, ">")
Rng7.Text = Format(AssyDwgRev, ">")
ActiveDocument.PrintOut
SerialNumber = SerialNumber + 1
ULLabel = ULLabel + 1
Counter = Counter + 1
Wend

'Recreate the bookmark ready for the next use.
With ActiveDocument.Bookmarks
..Add Name:="SerialNumber", Range:=Rng1
..Add Name:="ULLabel", Range:=Rng2
..Add Name:="PPONumber", Range:=Rng3
..Add Name:="ULLabelPrefix", Range:=Rng4
..Add Name:="SchematicRev", Range:=Rng5
..Add Name:="SpecRev", Range:=Rng6
..Add Name:="AssyDwgRev", Range:=Rng7
End With
'Next line added to update { REF SerialNumber }
Dim Fld As Field
For Each Fld In ActiveDocument.Fields
If Fld.Type = wdFieldRef Then Fld.Update
Next

End Sub

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~