Numbers on top of form

Hi Don,

The answer depends on whether there are multiple users on different machines, all of whom are creating new invoices using copies of your form, and you want the invoice numbers to always be consecutive and unique. If this is the case, then the best way would be to save the current invoice number to a network file that everyone has shared access to. Each time any user creates a new invoice, your code reads this file to get the most recent invoice number, increments it by 1, and writes it to the form, and re-writes it to the file and closes the file. To write the invoice number to a file, use VBA I/O statements (if you are unfamiliar with this, see VBA helps for Open, Write #, Print #, or Put #, and Close statements).

If your form will always be run from the same workbook (i.e., there are not multiple copies of the workbook), then you can simply save the invoice number in a cell on a worksheet. This cell can be hidden and protected, perhaps even on a hidden worksheet. Another place to save it in a workbook is in one of the workbook properties (you can even invent a new property for this). Check out the properties by going to File -> Properties -> Custom. These properties and their values can be accessed from within VBA.

To write the invoice number to the form, just put a Label control on the form and set its caption property to the invoice number (e.g., Label1.Caption = InvoiceNo, where InvoiceNo is a text string or number for the invoice). If you prefer to put the invoice number into the UserForm's banner, you can do this by setting its Caption property to the invoice number, e.g. UserForm1.Caption = "Invoice Number " & InvoiceNo.

I hope this is helpful.

Happy computing.

Damon