Forum Discussion
Automatically inserting a sequential invoice number
The code that I posted will only run when you open the workbook.
As an alternative, remove the Workbook_Open code.
Then select Insert > Module in the Visual Basic Editor.
Copy the following code into the module:
Sub PrintInvoices() Dim i As Long Dim n As Long n = Val(InputBox("How many copies do you want to print?")) For i = 1 To n With Worksheets("Sheet1").Range("D1") .Value = .Value + 1 End With Worksheets("Sheet1").PrintOut End With End SubRunning this macro will prompt you for the number of invoices to be printed. The invoice number will be incremented each time.
If you wish, you can assign the macro to a button on the sheet, or to a Quick Access Toolbar, or to a keyboard shortcut.
Let's say you want the invoice number in cell D1 on Sheet1.
Press Alt + F11 to activate the Visual Basic Editor.
Double-click ThisWorkbook under Microsoft Excel Objects in the Prpject Explorer pane on the left.
Copy the following code into ThisWorkbook:
Private Sub Workbook_Open()
With Worksheets("Sheet1").Range("D1")
.Value = .Value + 1
End With
End SubSwitch back to Excel.
Save the workbook as a macro-enabled workbook (*.xlsm).
You'll have to allow macros when you open it.
Each time you open the workbook, the invoice number will be increased by 1.
HansVogelaar hi. What if i also want to protect the cell so manually changing the invoice number will not be allowed. the code below works fine but if i protect the cell it gives an error
Hi, I have posted 5 macros in this discussion. Which one are you referring to?
- if I print multiple copies will each copy have a new number? or will the number only change when i reopen the file?
The code that I posted will only run when you open the workbook.
As an alternative, remove the Workbook_Open code.
Then select Insert > Module in the Visual Basic Editor.
Copy the following code into the module:
Sub PrintInvoices() Dim i As Long Dim n As Long n = Val(InputBox("How many copies do you want to print?")) For i = 1 To n With Worksheets("Sheet1").Range("D1") .Value = .Value + 1 End With Worksheets("Sheet1").PrintOut End With End SubRunning this macro will prompt you for the number of invoices to be printed. The invoice number will be incremented each time.
If you wish, you can assign the macro to a button on the sheet, or to a Quick Access Toolbar, or to a keyboard shortcut.
HansVogelaar This worked when I did the one where it changed every time we open the workbook but then I saw your code for printing copies in sequential order so I tried it but it isn't working for me, the printing option to print multiple copies of the invoice with sequential numbers is not coming up for me... what am I doing wrong?
