Build Inventory Management system Using Excel VBA
Ayman Elsaid Abdelwahed Khoshouey
Gulf Of Suez Petroleum Company
Maintenance engineering division
Ras Shoukair, Red Sea, Egypt
*Contact: khoshoueyae@gupco.net, phone +201018002225
Abstract — An Inventory Management system used to handle inventory items work like issue, add and transfer. There is many software on the market which the free license covered some of these work. You can now build with this simple article your own software easily using few lines of code. Even you do not have any previous experience of coding.
Introduction
In the beginning, we will add developer tab to Microsoft Excel to enable adding visual Basic code:
1- File menu.
2- Options choice.
3- Customize Rippon
4- Developer check box.
Fig. 1 enable adding visual Basic code developer tab
The main idea
The main idea of the application is to convert the Excel worksheet into database to allow modifing fields easely instead of using the complecated Access database files.We will link all database fields by using unique key word (Row number) which convert the ordinary cells relations of Excel to be single row relation columns values fixed database.
Fig. 2 the Form Main structure
I. DESIGN STEPS
Click developer tab and Click View Code then select Insert (UserForm)
a) Change property (Name) of the UserForm to be Main.
b) Change property (Caption) of the Main Form to be My Compny Inventory Management System.
c) Add Combobox control to the Main form.
d) Set the (RowSource) property of the Combobox to be (E:E) the column which contains the selectable values .
e) Add button to the worksheet with caption (Show).
f) Double click on the button to open code window and edit
Fig. 3 Form Main in running mode.
Now add a label control to the form and name it row_number
With caption is (Row:)
The next step is adding a new 6 TextBox controls (depending up on the number of fields you want to show on the form Main)
A. Adding 6 TextBoxes
B. Adjust the name & properties of the textboxes as shown
II. CODING STEPS
a) Double click on Combobox control to open the code window.
b) The code will opened in ComboBox change event.
c) Edit the next code in the change event and it will search on the selected value of any Combobox you build and return the related row variables values and show them in the linked textboxes fields.
Fig. 4 Form Main Show image of item.
III. USE YOUR IMAGINATION
You can add buttons to increase or decrease the quantity of the item to match the issue and transfer movement of item, just use the code below for the 2 buttons:
Code to add image of the selected item.
Regarding put the images in a folder and name each image with its item number. See the below picture.
Fig. 5 Location of items images database files.
Links to my published developed projects
https://www.udemy.com/user/ayman-khoshouey-2/
References
• Excel VBA Macro Programming by Richard Shepherd [1]
• VBA and Macros for Microsoft Excel (Business Solutions) by Bill Jelen and Tracy Syrstad [2]
• Office VBA Macros You Can Use Today by Juan Pablo Gonzalez, Cindy Meister, Suat Ozgur, Bill Dilworth. [3]
• VBA and Macros: Microsoft Excel 2010 (Mrexcel Library) by Bill Jelen, Tracy Syrstad [4]
• Writing Excel Macros With VBA by Steven Roman [5]
• Excel Macros for Dummies by Michael Alexander [6]
IV. CONCLUSIONS
This paper is a sample of how to use Microsoft Excel to developed complete applications using few code lines; you can find more details of these methods in the website (https://www.binarycodesoft.com)
