Unique ID number generation

  • Thread starter Thread starter Matt
  • Start date Start date
M

Matt

There is an invoive template in 97 that allocates a unique
number to the invoice when asked. I am trying to
replicate the code so that every time the sheet I have
created is opened and a command button used the next
number in a series is added to the sheet uniquely
identifying it. Any ideas please?
 
the key would be to use an incremental number and store the last used value.
What method you use to do this would depend on who will use the workbook as
the stored value must be visible to all sources that would use it. The
excel 97 template uses the registry I believe, so this would not be useful
if the workbook were to be placed on a LAN (as an example). If you will
only use it on one computer, then you can use getsetting and putsetting to
retrieve from and write to the registry. Otherwise, you might want to
create a textfile to store the number and read and write to that (and design
the code to avoid conflicts).
 
Adding to Tom, see if this can get you started. It's made for a multi user
environment:

Sub NewInvoiceNumber()
Dim ThisInvoice As Long
Dim ReadText As String
Dim StoreFile As String
'replace with network path\file name:
StoreFile = "C:\Temp\Number.num"
'read previous number:
If Dir(StoreFile) = "" Then 'not found
ThisInvoice = 1
Else
Open StoreFile For _
Input Access Read As #1
While Not EOF(1)
Line Input #1, ReadText
ThisInvoice = Val(ReadText)
Wend
Close #1
End If
ThisInvoice = ThisInvoice + 1
MsgBox "Invoice # " & ThisInvoice
'replace previous with "paste into sheet" code
'Store this number:
Open StoreFile For _
Output Access Write As #1
Print #1, ThisInvoice
Close #1
End Sub
 
Matt,

Another way might be to store the value in a workbook name.

This function which can be invoked from the commandbutton to generate the
next number, store it and return it.

Function GetId()
Dim myId As Long

On Error Resume Next
myId = Evaluate(Names("UniqueId").RefersTo) + 1
ActiveWorkbook.Names.Add Name:="UniqueId", RefersTo:="=" & myId
GetId = myId

End Function

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top