inputting sequential numbers

Posted by Scott on December 12, 2001 2:41 PM

I have created an invoice in excel and would like to have a new unique number assigned to the form. I do not save the forms, only print them. I would either click a button and assign the number, then print it. Or preferably have the number assigned when I run my print macro.

Any help would be greatly appreciated.

Scott

Posted by Gary on December 12, 2001 2:56 PM

You need someway of storing the last number used and each time assign the next one. You could use the VB functions SaveSetting and GetSetting to store this in the Registry.

There's some stuff on this at

http://j-walk.com/ss/excel/eee/eee020.txt

about creating a unique seqeuntial number for each instance of a template. Should give you some ideas.

Gary

Posted by Tom Urtis on December 12, 2001 3:05 PM

You can plug this line of code into your existing print macro, assuming your invoice is in A1 and you want to increment the invoice number by 1:

Range("A1").Value = Range("A1").Value + 1

Tom U

Posted by Tom Dickinson on December 12, 2001 3:10 PM

Try this:

Sub Macro1()
Dim Cntr, InvoiceNumber As Integer
Cntr = FreeFile()
Open "invoicenm.txt" For Input As #Cntr
Input #Cntr, InvoiceNumber
Range("B1") = InvoiceNumber
Close #Cntr

Open "InvoiceNm.Txt" For Output As #Cntr
Write #Cntr, InvoiceNumber + 1
Close
End Sub

This creates a text file. The first part opens the file and put the number into a variable. (It does not allow you to put the number directly onto the spreadsheet.) The second part saves the next invoice number into the file.

You may have to run the later part once initially to set up the file.

Posted by Scott - THANKS EVERYBODY on December 13, 2001 6:34 AM

Thanks everybody!!

I went witht he solution from Tom U.

Single line of code

Works great!!

Scott