| klste@hotmail.de |
Here everyone can create this program themselves.
The heading Create yourself warehouse management program in Excel VBA with article images and integrated supplier customer database and picture are two links to the homepage page with list of 527 links to YouTube movies.
The 527 YouTube movies describe how the program is created.
All VBA code are located on page Link to the page with code warehouse with databases and, if description is a code, simply mark the code, copy and paste in Excel file in the right place.
Before inserting the code you should make the line spacing as small as possible in a Word document.
Enter the code in Userform1 at the very end. Click Userform1 twice with the left mouse button. Mark and delete opened Private Sub UserForm_Click ().
In VBA editor code '''3_3_LmD########################################################################################################################### enter. (In the page with the code with the key combination ctrl + A select all and copy with ctrl + C. Paste into copied Word file, again highlight with keyboard shortcut Ctrl + A all, minimize line spacing, copy everything and copy copied into VBA Editor. what about code '''3_3_LmD###########################################################################################################################
Code contains checklist. If input mask is activated, the checklist checks whether all control elements are present, if a control element is not created or has been created incorrectly, for example, a message comes up: "Error in step 333!" The reported step should still be carried out. Because of this checklist you must not insert into half-finished program the main code, otherwise you have to shoot many messages.
This program has room for 1260 article numbers, 2047 storage bins, 100,000 booking records, 2000 withdrawal notes, 50,000 customer accounts and 3,000 supplier accounts. In the program each article is stored under its own subscription price, stored under a uniform average price and outsourced at its own subscription price.
This program has room for 1260 article numbers, 2047 storage bins, 100000 booking records, 2000 withdrawal notes, 50000 customer accounts and 3000 supplier accounts. In the program each article is stored under its own subscription price, stored under a uniform average price and outsourced at its own subscription price.
In program in the list box, the column widths adapt automatically to column widths of the displayed worksheet. Double-clicking the left mouse button displays the selected row in the active combo box in the list box.
In the program in date first is the day, then the month and after the month is the year.
1 This program is an Excel file programmed in the VBA. The first time this program is started, the yellow bar appears. The macros are activated when the "Activate Content" button is pressed.
2 The Excel file contains 4 worksheets: "Mask", "Table view", "Removal certificate", "Removal certificate list".
3 Everything is entered from the input mask. The mask consists of seven frames: "AS", "SD", "CD", "AR", "CR", "SR", "RC".
4 Within the scope "AS" Article and storage accounts you can create, edit or delete 1260 article accounts and 2047 storage account.
5 Three article accounts are created: Ar1 Chair Aaron, Ar2 Chair Black, Ar3 Chair Blue. Each article has unit "pc" (piece), reporting inventory and a picture. Created article accounts, if they have no stocks, you can change or delete. The password for deleting is the number 3. The article accounts can be evaluated in 6 criteria: Access, Removal, Article Account, Article/Storage bin, Article/Supplier, Article/Supplier/Storage bin.
6 Four bin accounts are created: Sb1, Sb2, Sb3 and Sb4. Created storage bin accounts, if they have no stocks, you can change or delete. The password for deleting is the number 3. The storage bin accounts can be evaluated in 3 criteria: Storage bin account, Storage bin/Article, Storage bin/suppliers.
7 Within the scope of the "SD" supplier database you can create, edit or delete 3000 supplier accounts.
8 Three supplier accounts are created: 60000, 60001, and 60002. Supplier accounts can be deleted if they do not already have any stock. The password for deletion is the number 3. The supplier accounts can be evaluated in 5 criteria: Inventory/Articles, Access/Articles, Removal/Articles, Supplier/Storage bin, Supplier/Article /Storage bin.
9 Within the "CD" customer database you can create, edit or delete 50000 customer accounts.
10 Three customer accounts are created: 100000 to 100002. You can delete customer accounts. The password for deleting is the number 3. The customer accounts can be evaluated in 2 criteria: Customer/Articles, Customer/Supplier.
11 In the "AR" Access/Related costs, receipts and reference costs are entered. Each booking record is entered in the land register, supplier account, product account, warehouse account. Program has room for hundreds of thousands of bookings.
12 Invoice IN-1 from the supplier 60000 has been booked: 200 items Ar1 Chairs Aaron worth 10000 €, storage bin Sb1.
13 Incoming invoice IN-2 from the forwarder, who has delivered the 200 items Ar1 Chairs Aaron from the supplier 60000. That cost 200 €.
14 Incoming invoice IN-3 from the supplier 60001 has been updated: 300 items Ar2 Chairs Black worth 25000 €, storage bin Sb2.
15 Incoming invoice IN-4 from the forwarder, who has delivered the 300 items Ar2 Chairs Black from the supplier 60001. That cost 300 €.
16 Invoice IN-5 from the supplier 60002 has been updated: 500 items Ar3 chairs Blue worth 18000 €, storage bin Sb3.
17 Incoming invoice IN-6 from the forwarder, who has delivered the 500 items Ar3 Chairs Blue from the supplier 60002. That cost 500 €.
18 In the frame each entry can be deleted, password for deleting is the number 3.
19 In the framework one can evaluate: supplier accounts, article accounts, storage place accounts.
20 In the "CR" Credit/rebates, credits and rebates are entered. Each booking record is entered in the land register, supplier account, product account, warehouse account.
21 Credit note CN-1 from supplier 60000 to the incoming invoice IN-1. 10% discount of 10000 €.
22 Credit note CN-2 from the supplier 60001 to the incoming invoice IN-3. 10% discount of 25000 €.
23 Credit note CN-3 from supplier 60002 to the IN-5 invoice. 10% discount of 18000 €.
24 In the frame each entry can be deleted, password for deleting is the number 3.
25 In the framework one can evaluate: supplier accounts, article accounts, storage place accounts.
26 Frame "SR" Stock rebooking. Here, such posting records are posted when items are transferred from one bin to another bin. Each booking record is entered in the land register, supplier account, item account, storage bin account.
27 Stock transfer order TO-1. 50 items Ar1 are provided for removal and relocated from storage bin Sb1 to storage bin Sb4 (issuing office).
28 Stock transfer order TO-2. 80 items Ar2 are provided for removal and relocated from storage bin Sb2 to storage bin Sb4 (issuing office).
29 Stock transfer order TO-3. 90 items Ar3 are provided for removal and relocated from storage bin Sb3 to storage bin Sb4 (issuing office).
30 In the frame each entry can be deleted, password for deleting is the number 3.
31 n the frame can evaluate: article accounts, supplier accounts, storage place accounts.
32 Within the frame of the "RC" Removal Certificate, withdrawal certificates are issued. Each booking record is entered in the land register, supplier account, item account, storage bin account and withdrawal note.
33 Removal certificate 1. Three customers, customer number from 100000 to 10002. For each customer, it is taken 2 Ar1, 4 Ar2 and 5 Ar3.
34 Table view Removal certificate 1.
35 Removal certificate 2. Three customers, customer number from 100000 to 10002. For each customer, it is taken 14 Ar1, 20 Ar2 and 10 Ar3.
36 Table view Removal certificate 2.
37 In the frame each entry can be deleted, password for deleting is the number 3.
38 In the frame you can evaluate: customer accounts, article accounts, supplier accounts, storage accounts.
39 Table view Journal
40 Table view Customer database
41 Table view Customer/Articles
42 Table view Customer/Supplier
43 Table view Current stock Art.
44 Table view Art_Access
45 Table view Art_Removal
46 Table view Article Account
47 Table view Article/Storage bin
48 Table view Article/Supplier
49 Table view Article/Supplier/Storage bin
50 Table view Supplier database
51 Table view Inventory/Articles
52 Table view Access/Articles
53 Table view Removal/Articles
54 Table view Supplier/Storage bin
55 Table view Supplier/Article /Storage bin
56 Table view Current stock St. bin
57 Table view Storage bin account
58 Table view Storage bin/Article
59 Table view Storage bin/suppliers
60 Table view Removal certificate. When Removal certificate number is selected and the "Position No." field is clicked.
61 Table view Removal certificate list. When Removal certificate number is selected. Each Removal certificate can have 40 positions.
62 Inventory reduction account is created in the customer database.
63 A piece of Ar1 has been destroyed. The piece is taken out as inventory reduction from the stock.
64 Inventory reduction account
65 From the article Ar2 was a piece more than is registered. This piece is entered as a stock increase with a minimum value of € 0,01 into the supplier account supplier 60001. Voucher is Si-1.
66 Yellow button "delete booking records". If 100,000 booking records are entered in the land register file, the posting records in the journal, in article accounts, in storage bin accounts are deleted. All reminder notes are also deleted. The password for deleting is number 3. The statistics are not deleted. After deletion, the program records all current stocks as the start bid in all article accounts and in all storage bin accounts.
67 With the red button on the top right "Delete everything" all booking records, Removal certificate notes and statistics are deleted. Password to delete is the number 3.
68 With the red button in the middle of the bottom "Delete everything" everything will be deleted, all databases, all booking records, all accounts. Password to delete is the number 3.
Old description ####################################
Warehouse management program in Excel with article pictures and integrated supplier customer database.
1 This program is an Excel file programmed in the VBA. When the program starts up for the first time, a yellow bar appears. The macros are activated when the "Activate content" button is pressed.
2 The file has 4 tablesheets: "Mask", "Table view", " Removal certificate", " Removal certificate list".
3 Everything is entered from the input mask. The mask consists of seven frames: "AS", "SD", "CD", "AR", "CR", "SR", "RC".
4 Within the scope "AS" Article and storage accounts you can create, edit or delete 1260 article accounts and 2047 storage account.
5 Three article accounts are created: Ar1 Chair Aaron, Ar2 Chair Black, Ar3 Chair Blue. Each article has unit "pc" (piece), report stock and a picture. The article accounts can be evaluated in 6 criteria: Access, Removal, Article account, Article/storage bins, Article/supplier, Article/supplier/storage bins.
6 Four storage space costs are created: Sb1, Sb2, Sb3 and Sb4. The storage space accounts can be evaluated in 3 criteria: Storage account, Storage bin/articles, Storage bin/suppliers.
7 Within the "SD" supplier database you can create, edit or delete 3000 supplier accounts.
8 Three supplier accounts are created: 60000, 60001, and 60002. The supplier accounts can be evaluated in 5 criteria: Inventory/Articles, Access/Articles, Removal/Articles, Supplier/Storage bin, Supplier/Article/Storage.
9 Within the "CD" customer database you can create, edit or delete 50000 customer accounts.
10 Fourteen customer accounts are created: 100000 to 100013. The customer accounts can be evaluated in two criteria: Customer/articles, Customer/supplier.
11 In the "AR" Access/Related costs, receipts and reference costs are entered. Each booking record is entered in the land register, supplier account, product account, warehouse account. Program has room for hundreds of thousands of bookings.
12 Input bill Ib-1 from the supplier 60000 has been booked: 200 Article Ar1 Chairs Aaron worth 10000 €, storage place Sb1.
13 Input bill Ib-2 from the forwarder who delivered the 200 items Ar1 chairs Aaron from the supplier 60000. That cost 200 €.
14 Input bill Ib-3 from supplier 60001 has been posted: 300 items Ar2 Chairs Black worth 25000 €, storage place Sb2.
15 Input bill Ib-4 from the forwarder who delivered the 300 items Ar2 Chairs Black from the supplier 60001. That cost 300 €.
16 Input bill Ib-5 from supplier 60002 has been booked: 500 items Ar3 Chairs Blue worth 18000 €, storage space Sb3.
17 Input bill Ib-6 from the forwarder who has delivered the 500 items Ar3 chairs blue from the supplier 60002. That cost 500 €.
18 In the frame each entry can be deleted, password for deletion is the number 3.
19 In the "CR" Credit/rebates, credits and rebates are entered. Each booking record is entered in the land register, supplier account, product account, warehouse account.
20 Credit memo CM-1 from the supplier 60000 to the Input bill Ib-1 10% discount of 10000 €.
21 Credit memo CM-2 from vendor 60001 to Input bill Ib-3 10% discount of 25000 €.
22 Credit memo CM-3 from vendor 60002 to Input bill Ib-5 10% discount of 18000 €.
23 Change the stock in the "SR" frame. Here, such posting records are posted when items are moved from one storage location to another storage location. Each booking record is entered in the land register, supplier account, product account, storage account.
24 Transit order TO-1. 50 articles Ar1 are provided for removal and are moved from storage location Sb1 to storage location Sb4 (output location).
25 Transit order TO-2. 80 items Ar2 are provided for removal and are moved from the storage location Sb2 to storage location Sb4 (output point).
26 Transfer order TO-3. 90 articles Ar3 are provided for removal and are moved from storage location Sb3 to storage location Sb4 (output location).
27 Within the framework of the "RC" Removal certificate, removal certificate are issued. Each booking record is entered in the land register, supplier account, product account, storage account and removal certificate.
28 Removal certificate 1. Thirteen customers, customer number from 100001 to 100013. For each customer, it is taken from 2 Ar1, 4 Ar2 and 5 Ar3.
29 Table view Removal certificate 1.
30 Table view Journal.
31 Table view Customer database.
32 Table view Customer/articles.
33 Table view Customer/supplier.
34 Table view Current stock art.
35 Table view Art_access
36 Table view Article_removal
37 Table view Article account
38 Table view Article/storage bins
39 Table view Article/supplier
40 Table view Article/supplier/storage bins
41 Table view Supplier database
42 Table view Inventory/Articles
43 Table view Access/Articles
44 Table view Removal/Articles
45 Table view Supplier/Storage bin
46 Table view Supplier/Article/Storage
47 Table view Current stock Storage bins
48 Table view Current Storage account
49 Table view Storage bin/articles
50 Table view Storage bin/suppliers
51 A piece of Ar1 has been destroyed. The piece is taken out of the stock as stock reduction.
52 Inventory reduction Account.
53 From the article Ar2 was a piece more than is registered. This piece is registered as a minimum stock of € 0.01.
54 If 100000 booking rates are entered in file, the entries are deleted with all withdrawal coupons. Password for deletion is 3. The statistics are not deleted. After deletion, the program records all current stocks as the start of the ending.
55 The "Delete everything" button deletes all booking records, removal notes and statistics.
56 With the buttons "Delete" and "Delete entry" deletes the selected article accounts, storage accounts, vendor accounts and customer accounts.
Old Program #####################################################
1_Warehouse management program based on an Excel - file with integrated supplier and customer database
With the program, you can 1260 manage article number 2047 campsites, 1 year make 1048010 booking rates, create approximately 25,000 withdrawal slips, and produce up to 40 positions in each sampling license.
6000 is the supplier accounts, highest number of which is the needs created or deleted. The supplier accounts are integrated in the program, in each posting record access or removal, should be, of which suppliers are the articles involved in the booking record. The integration allows conducting a detailed statistics and each time to find any article in the camp.
1047997 is the customer accounts, highest number of which is the needs created or deleted. The customer accounts are integrated in the program, if removal is recorded, to stand, to which customers, delivered the articles involved in the booking record, which enables a sales statistics for each customer to lead.
The program manages the stocks a year. After the end of the year, with button "change year" all booking records in the land and in all article and storage accounts are deleted and current opening balance on all articles and storage accounts, recorded.
2_Warehouse management program. Article and storage accounts. Excel VBA programming.
With the program you can manage 2047 item number, 1260 storage bins. Each article number and each storage bin have their own account. Articles - and storage bin accounts are created in the context of AS.
Article account is created when the item number, description and unit are registered. Reorder level can enter when the need be.
Storage account is created, if storage bin - name is entered.
Each account can be changed.
3_Warehouse management program. Integrated vendor and customer accounts. Excel VBA programming.
The supplier accounts, highest number of which is 6000, are integrated into the program. In each posting record access or removal. should be, from which suppliers are the articles involved in the booking record. The integration allows to conduct a detailed statistics and each time to find any article in the camp.
The customer accounts, highest number of which 1047997 is, are integrated in the program. In each removal certificate customer number is enables, for each customer sales statistics to lead.
4_Warehouse management program. Access and costs. Excel VBA programming.
The access and costs are entered by forming corresponding booking record. You can create with the program 1048010 booking rates in a year and save. Each record is entered under own ID number in the journal and all involved accounts.
5_Warehouse management program. Credits and discounts. Excel VBA programming.
The credits and discounts are entered by forming corresponding booking record. You can create with the program 1048010 booking rates in a year and save. Each record is entered under own ID number in the journal and all involved accounts.
6_Warehouse management program. Reposting. Excel VBA programming.
The reposting of the article within the warehouse by a storage bin on the other are entered by forming corresponding booking record. You can create with the program 1048010 booking rates in a year and save. Each record is entered under own ID number in the Journal and all involved accounts.
7_Warehouse management program. Removal certificate create or change. Excel VBA programming.
The removal of articles through creation of a removal certificate create create is recorded in the program. Any removal certificate can have 40 positions and change each time. The desired article is very easily found, enter only article number or article name and select the supplier. If the search criteria are entered, show program on which storage or storage bins of the desired articles is stored.
8_Warehouse management program. Analysis of the results. Excel VBA programming.
Constantly, the program analyzes the results of the business cases according to various criteria. The sheet with analysis are always available and ready to print.
9_Warehouse management program. Year change. Excel VBA programming.
The program manages the stocks a year. After the end of the year, with button "Year change" deletes all booking records in the Journal and in all article and storage accounts and posts current opening balance on all articles and storage accounts. Before you delete will be asked for the password, the password is the number 3.