| klste@hotmail.de |
Create Disposition program in Excel VBA yourself
Excel VBA. Disposition program with images and filter view in combination fields
This program shows current inventory of all items (Disposition Materials) and allows to find quickly and easily searched items (Disposition Material) in the warehouse (Disposition place).
The Excel file has 4 sheets: "Mask", "Disposition Material", "Disposition place" and "Sheet"
In the program everything is done from an input mask. The input mask is activated in the "Mask" sheet with "Mask" button.
Table sheet "Disposition Material" consists of 5 columns: Disposition Material-No., Description, Unit, Total, URL.
The first three columns (Disposition Material-No., Description, Unit) are entered directly in the worksheet and the remaining 2 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.
Worksheet "Disposition place" consists of 6 columns: Disposition place-No., Disposition place, Total, Disposition Material-No., Description, Unit.
In the first two columns (Disposition place-No., Disposition place) is entered directly in the worksheet by hand and the remaining 4 spades are filled 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.
The worksheet "Sheet" contains copies of the Journal, of the Disposition Material, of the Disposition place.
Input mask consists of two frames: " Material/Increase", " Material/Reduction", five buttons: "Delete entry", " Delete Journal", "Sheet", "Insert image", "Remove image", from six combo boxes: " ID-No.", " Disposition Material-No./Sheet", " Description/Sheet", " Disposition place-No./Sheet", " Disposition place/Sheet", " ID-No./Sheet", from a checkbox " Sheet ", from an image and from a list box.
In the frame " Material/Increase" only increase of the Material is registered. In the posting record, the program always writes the date, which is in the field "Date", if the field is empty the current date is entered. Number from the field "Quantity" is added to the selected asset. The number and the selected asset are added to the selected Disposition Material.
The whole booking record is entered in the Journal. Each booking record gets its own ID-No. The program creates 1048500 booking records.
In the frame "Material/Reduction" only reduction of the Material is registered. In the frame everything works as in the frame "Material/Increase" only the number of the quantity is deducted or registered as negative number.
All ID no. The booking records are in the "ID-No." field
If a Disposition Material-No. is selected in the frame " Material/Increase", the buttons "Insert image" and "Remove image" are visible. You can insert or remove an image for each article, the password will be asked for removal, that is the number 3.
List box can have different background colors. Blue for Disposition Material, yellow for Disposition place, white for Journal. With a double-click in the list field selected line is displayed in the active combo box, if background color is white, ID-No. displayed, if background color is blue, Disposition Material-No. is displayed if background color is yellow, Disposition place-No. is displayed. In the list box, the column widths adapt automatically to column widths of the worksheet shown.
Button "Sheet" activates sheet "Sheet" and creates in the sheet a copy of the sheet, which is displayed in the list box. In the sheet "Sheet" you can insert filters, click on cell A1, in menu bar “Home”, “Sort and Filter”, select “Filter”.
Checkbox "Sheet" activates the filter view in the combo boxes: "Disposition Material-No./Sheet", "Description/Sheet", "Disposition place-No./Sheet", "Disposition place/Sheet", "ID-No./Sheet". When filtering the " Disposition Material" sheet, the two combo boxes are visible from the top when filtering the " Disposition place" sheet, and the four combo boxes are visible from the top, when " Journal" worksheet is filtered, all five combo boxes are visible. All values selected in the five combo boxes are displayed in active combo boxes of the same color.
Each posting record, if it is still relatively up-to-date, can be deleted with the "Delete Entry" button, the password for delete is the number 3.
Content of the Journal is deleted with the button "Delete Journal". Delete password is the number 3.