Template number

Topic

New Reply

win 98se. excel 97.
I want to be able to generate a unique number in a cell everytime a template is opened to use as an invoice number.The number would need to remain the same in the saved invoice. This is probably not a hard question if you know the correct formula. Possibly “GetTemplateNumber”, but could not find any info on this one.
Please advise. Thank you.

Reply | Quote

Replies

You may need to use GetSetting and SaveSetting in a macro. You would need to set a macro to run when a new sheet based on the template was created which would get the last invoice number from the registry, increment it, and set the value of the relevant cell to that value. It would then save the new value back in the registry. This assumes only one user is using the spreadsheet, otherwise they will all be using different invoice numbers.

I am not aware of any command for putting a value back in the template although it might be possible to do that.

Another possibility would be to write the number out to a file and read it back in.

Reply | Quote

There are several ways to do this, and all of the ones I know about require a macro (VBA procedure). The macro would be tied to the Application.Newworkbook event. The macro would generate the invoice number and place it in the appropriate cell. How you generate this number depends on what you want the number to look like, and if the template is being used on more than one PC to generate invoices. If you want a sequential number, and the template is only used by one person, then you can keep the current invoice number in the Registry or in a file on the hard disk. When the Newworkbook event fires, the macro would retrieve the current number, add one to it, put the new invoice number in the appropriate cell, and write the new current number back to the Registry of the file. If the template is used on more than one PC and you want to insure unique sequential numbers, then the current number will have to be kept in a file shared on a file server by all of the PCs using the template. Another method would be to generate a invoice number from the date and time.

Reply | Quote