Auto numbering in Excel template

I am creating an order form in Excel and save as a template.  In this template which contain an order number in cell G1 and the initial number is 1000.  I want this number to be automatically increased by 1 every time I open through FILE/NEW. And the template will contain the number (1001...) for the next user.

I think you will come up some great idea to handle it.
mayleeaburgoonAsked:
Who is Participating?
 
byundtConnect With a Mentor Commented:
Hi mayleeaburgoon,
I suggest you consider writing the order number to an entry in your registry. This approach was discussed in http://oldlook.experts-exchange.com/Applications/MS_Office/Excel/Q_20783162.html with reference to John Walkenbach's web page discussing serializing http://j-walk.com/ss/excel/tips/tip60.htm 
Cheers!

Brad
0
 
joefunsmithConnect With a Mentor Commented:
If the registry solution doesn't work for you, I think I would choose to maintain the serial number in a database (Access or SQL) or absent access to a database, like this:

Put the template on the server.  You can have the workbook_open event in the template open a workbook, also on the server, which contains its own workbook_open event.  The 2nd workbook would open, increment a cell, save itself, and close itself.  The first workbook (created from the template would have a link to that cell.  To keep the number static, it would execute a paste value on the linked serial number cell and destroy the link.  

I haven't tried this myself, so there might be a whole in my logic here but offer it only as a possible solution.

joefunsmith
0
 
joefunsmithCommented:
by the way, you can still open the template on the server by using the File, New menu if you place something like "shortcut to myTemplate.xlt" in your template folder for each client machine.
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
Anne TroyConnect With a Mentor East Coast ManagerCommented:
There's a freebie download called MyInvoicing that is already set up to do this.

Smozgur and I created it.

http://www.theofficeexperts.com/downloads.htm#ExcelDownloads

If you can't use it in its entirety, you can steal the code that does the numbering.
:)
0
 
[ fanpages ]Connect With a Mentor IT Services ConsultantCommented:
Hi,

I replied to a similar posting a few weeks ago:

http://www.experts-exchange.com/Applications/MS_Office/Excel/Q_20742111.html

The below code uses a file ("invoice.txt") stored on a network (h) drive to record the next invoice number to be used.  Upon using the routine the file is updated accordingly so the next time the routine is called the "next" invoice number is returned.  Maybe this will help you?

Public Function Get_Next_Invoice_Number() As Double

  Dim dblReturn                                         As Double
  Dim hndFile                                           As Long
  Dim lngErr_Number                                     As Long
  Dim strErr_Description                                As String
  Dim strInput                                          As String
 
  On Error GoTo Err_Get_Next_Invoice_Number
 
  Const strFilename                                     As String = "h:\invoice.txt"
 
  dblReturn = 0#
  hndFile = FreeFile
 
  Open (strFilename) For Input Shared As hndFile
 
  Input #hndFile, strInput
 
  strInput = Trim$(strInput)
 
  If IsNumeric(strInput) Then
     dblReturn = CDbl(strInput)
  End If
     
  Close #hndFile
 
  hndFile = FreeFile
 
  Open (strFilename) For Output Shared As hndFile
 
  Print #hndFile, dblReturn + 1#
 
Exit_Get_Next_Invoice_Number:

  On Error Resume Next
 
  If hndFile <> 0& Then
     Close #hndFile
  End If
 
  Get_Next_Invoice_Number = dblReturn
 
  Exit Function
 
Err_Get_Next_Invoice_Number:

  lngErr_Number = Err.Number
  strErr_Description = Err.Description
 
  On Error Resume Next
 
  Select Case (lngErr_Number)
 
      Case (0&)
          Resume Next
         
      Case (53&), (62&)                                         ' File Not Found / Input Past End Of File
          Close #hndFile
         
          If Len(Trim$(Dir$(strFilename, vbNormal))) > 0 Then
             SetAttr strFilename, vbNormal
             Kill strFilename
          End If
         
          Open (strFilename) For Output As hndFile
          dblReturn = 1#
          Write #hndFile, dblReturn + 1#
     
      Case (70&)                                                ' Permission Denied
          MsgBox "Another process is using the Invoice Number file - please re-try later.", vbExclamation Or vbOKOnly, Windows(1).Caption
          dblReturn = 0#
         
      Case Else
          MsgBox "Error #" & CStr(lngErr_Number) & vbCrLf & vbLf & strErr_Description, vbExclamation Or vbOKOnly, Windows(1).Caption
          dblReturn = 0#
         
  End Select
 
  Resume Exit_Get_Next_Invoice_Number
 
End Function



BFN,

fp.
0
 
[ fanpages ]IT Services ConsultantCommented:
:)

You're doing a great job, Anne.

We appreciate your hard work even if the original question askers do not!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.