Excel

Basic invoice book

Ease of Use

Easy

Version tested with

2000, 2003 

Submitted by:

johnske

Description:

On opening the book an invoice form is shown with the current date. After printing the invoice the essential details are saved on another sheet, the current invoice is cleared and the invoice number incremented by one. The sheet is then ready for the details of the next invoice to be filled out. 

Discussion:

Those with small businesses that need a basic invoicing system that is easy to understand and use may find this useful. 

Code:

instructions for use

			

'This also illustrates the method of referring to cells 'by using "Shortcut Notation" where, for example, 'Range("A1") can be written simply as [A1] '(Read the VBA Help files for more on this...) Sub PrintInvoice() Dim Response AnotherCopy: Sheet1.[A1:I44].PrintOut Response = MsgBox("Do you need another copy?", _ vbYesNo + vbQuestion, "Confirmation") If Response = vbNo Then FillSalesList NewInvoice Else GoTo AnotherCopy End If Sheet1.Unprotect 'Increment the invoice number [G3] = [G3] + 1 Sheet1.Protect End Sub 'This saves details of the invoice on another sheet Private Sub FillSalesList() With Sheets("Sales").Columns(1).Rows(65536).End(xlUp) .Offset(1, 0) = Sheet1.[G3] .Offset(1, 1) = Sheet1.[G7] .Offset(1, 2) = Sheet1.[G5] .Offset(1, 3) = Sheet1.[I42] .Offset(1, 4) = Sheet1.[I43] .Offset(1, 5) = Sheet1.[I44] .Offset(1, 6) = Sheet1.[G1].Text End With End Sub 'Clears the invoice sheet Sub NewInvoice() With Sheet1 .Unprotect Cells.Locked = False 'Lock all the cells with formulae etc. [A11:I11, F1:F10, G3, H42:I44].Locked = True 'Clear details of last sale [A12:I40, G4:G10].ClearContents [B12].Select .Protect End With End Sub

How to use:

  1. (Not applicable, the ranges in the code are specific to the attachment)
 

Test the code:

  1. Download the attachment and unzip to wherever you find preferable
  2. Open the wokbook "InvoiceBook" and insert your logo in the text box (Top Left)
  3. If you want to delete this text box, go to Tools/Protection/Unprotect Sheet (no password)
  4. Now Left-click the edge of the text-box, then Right-click and select Cut, then Save...
  5. Complete the details for your invoice
  6. If you want to view details on the Sales sheet, click the "Sales" sheet tab at the bottom
  7. If you want to change things such as Titles/Headings/Invoice Number, the Invoice sheet must be unprotected first (as in line 3 above)
  8. (It will be re-protected on clicking either "New Invoice" or "Print").
 

Sample File:

InvoiceBook.zip 14.32KB 

Approved by mdmackillop


This entry has been viewed 517 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express