| klste@hotmail.de |
Create stock program 50 000 article and 50 000 storage bins in Excel VBA yourself
Excel VBA. Stock program 50 000 article and 50 000 storage bins with filter view in the list box
With the program, you can manage warehouses with 50 000 items and 50 000 storage bins.
The Excel file has 4 worksheets: "Mask", "Article", "Removal" 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.
The "Article" spreadsheet consists of 10 spades: Art.-No, Description, Unit, Inventory, Total value the Inventories, Average price, Storage bins, Art.-No, Designation, Inventory.
Three first columns and column "G" (Art.-No, Description, Unit and Storage bins) are entered directly in the worksheet and the remaining 7 spades are filled in by the program.
The worksheet is locked and password protected (password is the number 3). If this is to be unlocked, select "Review" in the menu bar "Unprotect Sheet", enter the number 3 in the input box Password and click OK.
In the worksheet "Removal" there is always the last selected Removal certificate.
The sheet "Sheet" contains copies of the journal, storage bins, and a list of all Removal certificates.
Input mask consists of two frames: "Access/Cost", "Removal certificate", and five buttons: "Delete Entry", "Delete List", "Delete Journal", "Delete article inventory", "Sheet" and six checkboxes: "Article", "Journal", "Removal certificate", "List","Storage bin"," Sheet "and from a list box.
The "Access/Cost" frame is for Additions, Reference Costs, and Credit / Discount.
Each booking record gets its own ID number. Program creates 1048500 booking records. All ID-No. are in the field " ID-No." With an empty field, you can create a new booking record and enter, with the field filled in, the selected booking record is displayed in lower fields. If incorrect ID-No. is entered, all lower fields are emptied.
Date from field "Date" is entered in the booking record, if field is empty, current date is entered.
The "Business case" field determines the type of posting record: On Access - quantity and amount are entered; for Cost - only amount is entered; for Credit/Discounts - the amount is deducted.
In the fields "Article No." and "Description" the contents of the columns "A" and "B" are from the worksheet "Article". Two more yellow labels show the unit and price of the selected item.
Contents of the fields "Quantity" and "Amount" are recorded as a number in the entry, the empty contents are recorded as zero.
The field "Storage bin" contains the contents of the column "G" from the worksheet "Article".
Documents are entered in the "Document" field.
If ID-No. Field is empty, the "Enter" button is visible and you can enter a new booking record.
The removal certificates are created in the frame "Removal certificate".
All numbers of removal certificate are in the field "Removal No.". New removal certificate is created when the field is empty and is processed when removal certificate number is in the field.
In the field "Position" are all position numbers of the selected removal certificate. New position is created when “Position” field is empty. Program creates only 20 positions in a removal certificate.
The yellow fields for article number work the same way as those fields in the "Access/Cost" frame.
The Quantity field works the same way as the field in the “Access/Cost” frame.
Field "Amount" is a label, it says that what program has calculated.
The field "Storage bin" works in the same way as such field in the frame "Access /Cost".
If "Position" field is empty, " Enter" button is visible, and you can create new position.
Button "Removal certificate" activates sheet "Removal" and turns off the input mask.
List box can have different background colors. Yellow for article number, white Journal, green for removal certificate, brown for list and gray for storage bin.
If the checkbox "Sheet" is not active, you can double-click selected ID-No. Transfer field in "ID-No.", selected removal certificate "Removal No." Transfer field, transfer selected article number to "Article No." fields, transfer selected storage bin to “Storage bin” fields.
In the list box, the column widths adapt automatically to column widths of the worksheet shown, (if list box is brown this will not work).
The "Sheet" button is only visible if list box Journal, List or Storage bin. 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 & Filter, select Filter.
Checkbox "Sheet" activates filter view in the list box. If the Journal is filtered, only filtered ID numbers will appear in the ID-No. field. When the list is filtered, only the withdrawn Removal certificate number will appear in the field "Removal No.".
Checkbox "Storage bin" shows all storage bins in the list box.
Checkbox "List" shows in the list box all Removal certificates.
Checkbox "Removal certificate" shows the selected removal certificate in the list box.
Checkbox "Journal" shows the journal in the list box.
Checkbox "Article" shows all articles in the list box.
Delete Entry button deletes selected booking record. Password is the number 3.
Delete List button deletes all removal certificates. Password is the number 3.
Button "Delete Journal" deletes contents of the journal. Password is the number 3.
“Delete article inventory” button clears column contents in the “Article” worksheet: D, E, F, H, I, J. Password is the number 3.