customizing sequential numbers in Word

[Meg]

I've read through the stings and have a general idea how to create sequential
numbering using a Form in Word.

The form I've created, which I saved as a templete, is for data requests.
So, anytime a requester opens the template, a new tracking number would be
created.

I was hoping to customize the numbering scheme so anytime someone opens a
template it would generate a tracking number with the person's intials
followed by the date and the next number..e.g., DM_Oct132009_1 and so on.
This is just an example, I'm not sure if this is possible..any
recommendations?

 

[Jay Freedman]

I've read through the stings and have a general idea how to create sequential
numbering using a Form in Word.

The form I've created, which I saved as a templete, is for data requests.
So, anytime a requester opens the template, a new tracking number would be
created.

I was hoping to customize the numbering scheme so anytime someone opens a
template it would generate a tracking number with the person's intials
followed by the date and the next number..e.g., DM_Oct132009_1 and so on.
This is just an example, I'm not sure if this is possible..any
recommendations?

Let's assume you're using the macro-and-settings-file scheme in
http://word.mvps.org/FAQs/MacrosVBA/NumberDocs.htm. In that macro,
this line is responsible for writing the unique document number into
the document at a bookmark:

ActiveDocument.Bookmarks("Order").Range.InsertBefore Format(Order,
"00#")

The part of this up to "InsertBefore" is the command that determines
where to write the number; the remaining part, Format(Order, "00#"),
supplies the number. What you want to do is modify the number.

Putting in the date is the easy part. This expression creates the date
in the format you showed, followed by the underscore:

Format(Now, "MMMddyyyy_")

So the date and the number together would be

Format(Now, "MMMddyyyy_") & Format(Order, "00#")

I can't tell you how to add the person's initials until you explain
whether those initials (or the person's full name, from which the
initials could be extracted) exist somewhere in the form -- are they
in a text form field, and if so, what is that field's name?

 

[Meg]

Hi Jay-

Thank you for your quick response. I posted a response earlier, but it
didn't show up.

To answer your question, I created a text box for the project mananger to
enter their name. I would like their intitials to show up in the tracking #
when they first open the template.

In the link you provided, there is a reference to a .txt - do you create
this text file with a list of numbers? I'm not clear on the role of the .txt
document.

Thank you again! Meg

 

[Meg]

Hi Jay-

Thank you for your quick response. In the form I created, I have a field
called Project Mgr Name. This is the person's initials I wanted to assign to
the tracking number.

 

[Jay Freedman]

Hi Meg,

This may get a bit deep if you don't have any experience with macros, but
I'll try to make it easier.

First, to answer the question in your other reply: The macro in the mvps.org
article uses a file "C:\Settings.txt" to store the current value of the
"order number". If the file doesn't exist, the macro will create it. The
name and folder location of the file aren't important -- you can name it as
you like and store it anywhere on the disk -- the only requirement is that
both references to it in the macro are exactly the same.

The forma field name can't be literally "Project Mgr Name" because spaces
aren't allowed. Look again in the Properties box of the field; it might be
that the spaces are just removed to make ProjectMgrName, or underscores are
used to make Project_Mgr_Name. In the following code sample, I've used the
former version.

The code in your macro should take account of a couple of possible snags.
The user might not have filled in the project manager name field yet, so
there wouldn't be anything to get initials from. If the field isn't blank,
it might have just one name -- maybe the person's surname -- instead of two.
Or it might have several, like the scientist Johannes van der Waals. I chose
to show how to extract one initial or at most two. In addition to the code
from the mvps.org article that gets and saves the Order number, you need
code like this:

Dim ProjMgrName As String
Dim ProjMgrInit As String
Dim LastSpacePos As Long
Dim TrackNum As String

ProjMgrName = _
Trim(ActiveDocument.FormFields("ProjectMgrName").Result)

If Len(ProjMgrName) = 0 Then
' field is empty
MsgBox "Please enter Project Manager name"
Exit Sub
End If

If InStr(ProjMgrName, " ") = 0 Then
' field has no spaces -- take only first letter
ProjMgrInit = Left(ProjMgrName, 1)
Else
' field has one or more spaces
' find location of last space, get second initial from next letter
LastSpacePos = InStrRev(ProjMgrName, " ")
ProjMgrInit = Left(ProjMgrName, 1) & _
Mid(ProjMgrName, LastSpacePos + 1, 1)
End If

TrackNum = UCase(ProjMgrInit) & _
Format(Now, "_MMMddyyyy_") & _
Format(Order, "0#")

 

[Jay Freedman]

Hi Meg,

I hope you don't think I abandoned you. There has been a breakdown in the
transfer of posts from the NNTP gateway that most MVPs use and the Web
forums. The last transfer was on October 13, and it still isn't fixed.

This is a copy of the reply I posted to you on October 15, not realizing
that it wouldn't be visible to you.

Jay

---------------------------

This may get a bit deep if you don't have any experience with macros, but
I'll try to make it easier.

First, to answer the question in your other reply: The macro in the mvps.org
article uses a file "C:\Settings.txt" to store the current value of the
"order number". If the file doesn't exist, the macro will create it. The
name and folder location of the file aren't important -- you can name it as
you like and store it anywhere on the disk -- the only requirement is that
both references to it in the macro are exactly the same.

The forma field name can't be literally "Project Mgr Name" because spaces
aren't allowed. Look again in the Properties box of the field; it might be
that the spaces are just removed to make ProjectMgrName, or underscores are
used to make Project_Mgr_Name. In the following code sample, I've used the
former version.

The code in your macro should take account of a couple of possible snags.
The user might not have filled in the project manager name field yet, so
there wouldn't be anything to get initials from. If the field isn't blank,
it might have just one name -- maybe the person's surname -- instead of two.
Or it might have several, like the scientist Johannes van der Waals. I chose
to show how to extract one initial or at most two. In addition to the code
from the mvps.org article that gets and saves the Order number, you need
code like this:

Dim ProjMgrName As String
Dim ProjMgrInit As String
Dim LastSpacePos As Long
Dim TrackNum As String

ProjMgrName = _
Trim(ActiveDocument.FormFields("ProjectMgrName").Result)

If Len(ProjMgrName) = 0 Then
' field is empty
MsgBox "Please enter Project Manager name"
Exit Sub
End If

If InStr(ProjMgrName, " ") = 0 Then
' field has no spaces -- take only first letter
ProjMgrInit = Left(ProjMgrName, 1)
Else
' field has one or more spaces
' find location of last space, get second initial from next letter
LastSpacePos = InStrRev(ProjMgrName, " ")
ProjMgrInit = Left(ProjMgrName, 1) & _
Mid(ProjMgrName, LastSpacePos + 1, 1)
End If

TrackNum = UCase(ProjMgrInit) & _
Format(Now, "_MMMddyyyy_") & _
Format(Order, "0#")

 

[Meg]

Hi Jay -

Thank you again for all your help. I was out of the office last week and
just saw your reply. No worries about abondoning me, I don't think that at
all. I was having some issues with the post, I contributed mine to 'user
error.'

Regards, Meg