Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unique ID number generation
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unique ID number generation
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). -- Regards, Tom Ogilvy "Matt" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unique ID number generation
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 -- HTH. Best wishes Harald Followup to newsgroup only please "Matt" skrev i melding ... 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unique ID number generation
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) "Matt" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
RANDOM NUMBER GENERATION. | Excel Worksheet Functions | |||
Help for Random Number Generation | Excel Worksheet Functions | |||
random number generation | Excel Discussion (Misc queries) | |||
random number generation | Excel Worksheet Functions | |||
random number generation | Excel Worksheet Functions |