Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-17-2012, 05:40 AM
PhilSmith1981 PhilSmith1981 is offline Automated reference numbering Windows 7 64bit Automated reference numbering Office 2010 64bit
Novice
Automated reference numbering
 
Join Date: Dec 2012
Posts: 2
PhilSmith1981 is on a distinguished road
Default Automated reference numbering

Hi,

I am developing a template document to be used for reports and one of the requirements is to have a unique number for each report, something along the lines of 'HSRep001'.



Is there a macro which will enable such a number to be generated each time the template is opened/saved.

Regards.
Reply With Quote
  #2  
Old 12-17-2012, 07:06 AM
gmaxey gmaxey is offline Automated reference numbering Windows 7 32bit Automated reference numbering Office 2010 (Version 14.0)
Expert
 
Join Date: May 2010
Location: Brasstown, NC
Posts: 1,598
gmaxey is just really nicegmaxey is just really nicegmaxey is just really nicegmaxey is just really nicegmaxey is just really nice
Default

An AutoNew macro can be used to assign an up seqeuncing number to each new document based on the template. You will first need to set the cursor where you want the number to appear and run the "InvoicerSetup" procedure:

Code:
Option Explicit
Dim oBMs As Bookmarks
Dim lngCount As Long
Dim strNumber As String
Dim strLeadingText As String
Dim strTrailingText As String
Sub AutoNew()
  AddSeqNumFromRegistry
lbl_Exit:
  Exit Sub
End Sub
Sub InvoicerSetup()
  Set oBMs = ActiveDocument.Bookmarks
  'Setup the bookmark
  If MsgBox("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 empty bookmark
    Selection.Collapse wdCollapseStart
    Selection.Bookmarks.Add "SeqNum", Selection.Range
  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
  'Seup/Reset starting sequence number.
  strNumber = GetSetting("Simple Invoice", "Settings", "Number")
  strNumber = InputBox("Set/reset startinbg sequence number?  The" _
     & " current next number is: " & Val(strNumber), _
     "Sequence Number", Val(strNumber))
  SaveSetting "Simple Invoice", "Settings", "Number", strNumber
  'Setup leading text
  strLeadingText = InputBox("Add leading text?", "Leading Text", "Invoice No: ")
  SaveSetting "Simple Invoice", "Settings", "Leading Text", strLeadingText
  strTrailingText = InputBox("Add trailing text?", "Trailing Text")
  SaveSetting "Simple Invoice", "Settings", "Trailing Text", strTrailingText
  'Do the deed
  AddSeqNumFromRegistry
lbl_Exit:
  Exit Sub
End Sub
Sub AddSeqNumFromRegistry()
Dim BMRange As Range
  Set oBMs = ActiveDocument.Bookmarks
  lngCount = Val(GetSetting("Simple Invoice", "Settings", "Number"))
  strLeadingText = GetSetting("Simple Invoice", "Settings", "Leading Text")
  strTrailingText = GetSetting("Simple Invoice", "Settings", "Trailing Text")
  strNumber = Format(lngCount, "000")
  If strTrailingText <> "" Then
    strTrailingText = " " & strTrailingText
  End If
  'Identify current Bookmark range and insert text
  On Error GoTo Handler:
  Set BMRange = oBMs("SeqNum").Range
  BMRange.Text = strLeadingText & " " & strNumber & strTrailingText
  'Re-insert the bookmark
  oBMs.Add "SeqNum", BMRange
  ActiveDocument.Fields.Update
  lngCount = lngCount + 1
  strNumber = CStr(lngCount)
  SaveSetting "Simple Invoice", "Settings", "Number", strNumber
  Exit Sub
Handler:
  If Err.Number = 5941 Then
    MsgBox "The SeqNum bookmark has been deleted. You must run" _
      & " the InvoicerSetup routine to redine the bookmark?", _
      vbOKOnly, "Missing bookmark!"
    Exit Sub
  End If
End Sub
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
  #3  
Old 12-18-2012, 09:00 AM
PhilSmith1981 PhilSmith1981 is offline Automated reference numbering Windows 7 64bit Automated reference numbering Office 2010 64bit
Novice
Automated reference numbering
 
Join Date: Dec 2012
Posts: 2
PhilSmith1981 is on a distinguished road
Thumbs up Automated Reference Numbering

gmaxey,

Thank you very much, this has worked a treat.

Regards.
Reply With Quote
Reply



Similar Threads
Thread Thread Starter Forum Replies Last Post
Automated reference numbering Automated pst file backup jbarr11 Outlook 1 04-17-2011 05:28 PM
Automated reference numbering Error Message in automated emails Nick1 Outlook 1 02-10-2011 09:39 AM
Automated reminders paulgy80 Outlook 1 08-21-2010 04:35 PM
Automated Letter Sections gladfan Word 0 08-11-2010 01:53 AM
Automated Functions?? nickypatterson Outlook 0 08-27-2009 01:50 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 05:17 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft