The main steps for creating this application are as follows:
First, place the necessary data related to warehouse organization onto Excel worksheets:
- Create six sheets in the workbook: prod_pit, Storeage, shv_izd, aud_vid_texn, mebel, kanc_tov.
- On the Storeage sheet, place the service information required for the program’s operation (the number of product categories, the names of the product categories—food products, sewing items, audio/video equipment, furniture, office supplies—the number of items in stock for each category, the number of warehouse clerks, and their surnames).
- The other five sheets will store the actual data about the goods in the warehouse.
- For each type of product, the following information is stored: product name, weight/quantity, date of receipt into the warehouse, date of release from the warehouse, the warehouse clerk who accepted the goods, the clerk who released the goods, storage cost, and payment confirmation (yes/no).
Next, proceed to developing the main form of the warehouse application:
- Open the VBA code editor.
- Add a user form (via Insert | UserForm). Set the form’s Name property to MainForm.
- Place the necessary controls on the form and set their properties according to Table, so that they appear similar.
- Write code in the form module to handle events for the controls:
- From the drop-down list, select the product category.
- The corresponding goods stored on different worksheets will then appear in the list box below.
- The Receive Goods and Release Goods buttons should open forms that support the respective actions of receiving and releasing goods.
Table. Property values for MainForm and its controls set in the Properties window
| Object | Property | Value |
| Form | Name | MainForm |
| Caption | Warehouse | |
| Label | Name | Label1 |
| Caption | Goods Warehouse | |
| ComboBox | Name | ComboBox1 |
| Style | fmStyleDropDownList | |
| Button | Name | CommandButton1 |
| Caption | Receive Goods | |
| Button | Name | CommandButton2 |
| Caption | Release Goods | |
| ListBox | Name | ListBox1 |
| BoundColumn | 1 | |
| ColumnCount | 1 | |
| ColumnHeads | False |
The next step is to design the form for receiving goods and the form for releasing goods. The process here is similar to that used for developing the main form:
- configure the appropriate properties for the controls,
- and write the corresponding event-handling code.
Finally, place a button on the Storeage worksheet that opens the main form MainForm.
Also, make sure to enter the necessary code in the worksheet module to handle the button click.