Create Invoice program with journal, customer database and product range in Excel VBA yourself

Excel VBA. Invoice program with journal, customer database and product range

With the program you can create and edit 10 000 invoices. Each Invoice can have 40 positions. The program has integrated customer database for 50 000 customer accounts and integrated product range for 50 000 product accounts. In the program each booking record is entered in journal.

The Excel file has 5 worksheets: "Mask", " Customer database", " Product range", "Form" and "Sheet".

In the program everything is done from an input mask. The input mask is activated in the "Mask" worksheet with the "Mask" button.

Sheet "Customer database" consists of 11 spades: Customer No., Company name, Additional name, Address, First name/Name, Street, Postal code/Location, Country, E-mail, Phone/Fax, Note. All data are entered directly in the worksheet by hand

Sheet "Product range" consists of 6 spades: Article No., Description, Unit, Price, VAT%, Gross. All data are entered directly in the worksheet by hand

The sheet "Form" always contains the last selected invoice.

The "Sheet" sheet can contain copies of the journal, customer database, product range, and list of all invoices.

The input mask consists of two frames: "Create Invoice", "Mark Invoice", Combobox "ID No." and four buttons: "Delete Entry", "Delete Invoices", "Delete Journal", "Sheet" and six Checkboxes: "Customer database", "Product range", "Form", "Invoices", " Journal", "Sheet" and from a list box.

Invoices are created and edited in the "Create Invoice" frame. All invoice numbers are in the field "Invoice No.". New Invoice will be created if the field is empty and will be processed if invoice number is in the field. Date from field "Date" is entered in the booking record, if field is empty, current date is entered. The field "Customer No." contains the contents of the columns "A" "from the" Customer database "worksheet. In the fields "Article No." and "Description", the contents of columns "A" and "B" are from the "Product range" worksheet. Two more yellow labels show price and VAT of the selected item. If the number of the selected article is entered in the field "Quantity" and the field is left, the program calculates the total net price and gross price, the calculated prices are in the fields "Total" and "Gross". If the "Position" field is empty, the " Enter" button is visible, and you can enter new item number of the selected invoice. With the "Form" button, the input mask is hidden and the form sheet is activated. In the worksheet is or no invoice or last selected invoice.

In the "Mark invoice" frame, invoices created are marked as "Paid" or "Canceled". All numbers of generated invoices are in the field "Invoice No.". If an invoice number is selected, in blue boxes: "Created on", "Total net", "Total VAT.”, “Total gross”, in the gray field "Customer No." and in the green field "Position" are data from the invoice, which are not changed in the frame. And in brown fields: "Mark", "Marked on", "Document", "Note" you can enter new data and with the button  Enter" you can add all new data to the selected invoice. With the "Form" button, the input mask is hidden and the form sheet is activated. In the worksheet is or no invoice or last selected invoice.

Each booking record gets its own ID-No.. Program creates 1048500 booking records. All ID-No. are in the "ID-No." field If this field is empty, the "Delete Entry" button is foreign. If this field is filled in, the "Delete Entry" button is visible.

List box can have different background colors. Gray for customer database, yellow for product range, green for form, brown for invoices and white for journal. In the list field you can double-click selected customer number in combo box "Customer No.", selected article number in field "Article No.", selected invoice number in fields "Invoice No.", selected ID-No. in the field " ID-No.". In the list box, the column widths adapt automatically to column widths of the shown worksheet (if list box is brown, this only works for first 11 columns).

The "Sheet" button is only visible if the list box shows the customer database, product range, invoices or journal. The button activates sheet "Sheet", creates a copy of the sheet displayed in the list box in the sheet, and turns off the input form. In the sheet "Sheet" you can insert filters, click on cell A1, in menu bar Home, Sort and Filter, select filter. Checkbox "Sheet" activates filter view in the list box. If the customer database is filtered, only filtered customer numbers are displayed in the "Customer No." Combo box. If product range is filtered, only filtered article numbers are displayed in the "Article No.” Field. If invoices are filtered, the fields "Invoice No." are displayed Filtered invoice number, if journal is filtered, only filtered ID-No. are in the field "ID-No.".

Checkbox "Journal" shows the journal in the list box.

Checkbox "Invoices" shows all invoices in the list box.

Checkbox "Form" shows in the list box of selected invoice.

Checkbox "Product range" shows the product range in the list box.

Checkbox "Customer database" shows the customer database in the list box.

“Delete Entry” button deletes selected booking record. Password is the number 3.

“Delete invoices” button deletes all invoices. Password is the number 3.

Button "Delete Journal" deletes contents of the journal. Password is the number 3.