Hello!
I would like to ask you for help programming inventory management system in excel using VBA and userform. I would like to form one easy excell file, which gives you information about stock level (I am starting with small business for parents and my 2 parents besides me will pack products, we live on different locations, so I need some inventory management system).
I have no experience with programming, but I am trying using youtube videos and forums to achieve my goal. Unfortunately my effort gives no result - I just managed to program four command buttons in excell sheet (two for opening userform and two for closing userform) and find appropriate SUMIF function to gather stock information in one table (besides that I just used some conditional formatting). I am typing code for other command buttons for two weeks, but it does not work.
Screenshoots are below and temporary situation is as follows:
* one excell file
* two sheets
- first sheet STOCK which contains stock level table and two command buttons - left button PACKING for pack
products and send it via post (product goes out, e.g. leaves stock) - right button SUPPLY for receive
products from me, when I give them (fulfilling stock, shortage of stock... so product goes in, e.g. stock gets
bigger, more products to be in stock)
- second sheet INVENTORY which contains table with all informations about products that have leave or come
in stock (it has 5 columns)
* two userforms (PACKING and SUPPLY) which are shaped as seen on picture. Eachother already appears when
clicking appropriate command button in STOCK sheet. Like I said, if I click EXIT button it dissapears.
* two or three FRAMES - all option buttons are made in such way, that they are shaped as group in some frame=>
so options buttons for PRODUCT togeter, options buttons for QUANTITY together, same for DESCRIPTION (I read
some advice it should be like this - the name of frame is same as label in front of or above option buttons)
So I would like to help me write code for left command button (PACK (OUT) and STOCK (IN)) in userforms, which will
contain following:
* transfer data in first empty row and transfer in appropriate column
* transfer caption of optionbutton or what is typed in textbox
* default values:
- textbox DATE => date and hour of entry (as I saw it is function NOW() )
- frame DESCRIPTION options => default option should be SELL (userform PACKING) and SUPPLY (userform
SUPPLY) - both options are in red
- frame QUANTITY options => default option should be "-1" (userform PACKING) - this option is in red
* message box (so it is unable to transfer data into table in REGISTRY sheet, if message box appears, e.g. something
is wrong => missing entry or some textbox/option is empty/not selected):
- frame PRODUCT => if no option button is selected, then message "SELECT ONE PRODUCT"
- textbox QUANTITY (userform SUPPLY) => if no number is entered, then message "ENTER NUMBER OF
PRODUCT"
- option button OTHER in frame DESCRIPTION => if this option button is selected, then textbox NOTES must
not be empty (some text must be written). So if it is empty, then message "ENTER NOTES"
Labeling used in subwindow PROPERITIES in first row (Name) in VBA window (alt+F11):
* USERFORM => userform_packing, userform_suppy
* TEXTBOX => txt_date, txt_notes, txt_quantity
* FRAME => frame_description, frame_product
* OPTION BUTTON => DESCRIPTION; opt_sell, opt_supply, opt_gratis, opt_correction, opt_transfer, opt_other//
PRODUCTS; opt_vit, opt_cit, opt_golt, opt_supt, opt_get1, opt_get4, opt_flt, opt_lovt, opt_tuma, opt_taka
* COMMAND BUTTON => cmd_pack, cmd_supply, cmd_exit
I would appreciate any help, if someone could write code or if I could correct assemble code from appropriate written parts of code.
Hope my post is not too long and it is enough clear and concise.
Thanks in advance!
I would like to ask you for help programming inventory management system in excel using VBA and userform. I would like to form one easy excell file, which gives you information about stock level (I am starting with small business for parents and my 2 parents besides me will pack products, we live on different locations, so I need some inventory management system).
I have no experience with programming, but I am trying using youtube videos and forums to achieve my goal. Unfortunately my effort gives no result - I just managed to program four command buttons in excell sheet (two for opening userform and two for closing userform) and find appropriate SUMIF function to gather stock information in one table (besides that I just used some conditional formatting). I am typing code for other command buttons for two weeks, but it does not work.
Screenshoots are below and temporary situation is as follows:
* one excell file
* two sheets
- first sheet STOCK which contains stock level table and two command buttons - left button PACKING for pack
products and send it via post (product goes out, e.g. leaves stock) - right button SUPPLY for receive
products from me, when I give them (fulfilling stock, shortage of stock... so product goes in, e.g. stock gets
bigger, more products to be in stock)
- second sheet INVENTORY which contains table with all informations about products that have leave or come
in stock (it has 5 columns)
* two userforms (PACKING and SUPPLY) which are shaped as seen on picture. Eachother already appears when
clicking appropriate command button in STOCK sheet. Like I said, if I click EXIT button it dissapears.
* two or three FRAMES - all option buttons are made in such way, that they are shaped as group in some frame=>
so options buttons for PRODUCT togeter, options buttons for QUANTITY together, same for DESCRIPTION (I read
some advice it should be like this - the name of frame is same as label in front of or above option buttons)
So I would like to help me write code for left command button (PACK (OUT) and STOCK (IN)) in userforms, which will
contain following:
* transfer data in first empty row and transfer in appropriate column
* transfer caption of optionbutton or what is typed in textbox
* default values:
- textbox DATE => date and hour of entry (as I saw it is function NOW() )
- frame DESCRIPTION options => default option should be SELL (userform PACKING) and SUPPLY (userform
SUPPLY) - both options are in red
- frame QUANTITY options => default option should be "-1" (userform PACKING) - this option is in red
* message box (so it is unable to transfer data into table in REGISTRY sheet, if message box appears, e.g. something
is wrong => missing entry or some textbox/option is empty/not selected):
- frame PRODUCT => if no option button is selected, then message "SELECT ONE PRODUCT"
- textbox QUANTITY (userform SUPPLY) => if no number is entered, then message "ENTER NUMBER OF
PRODUCT"
- option button OTHER in frame DESCRIPTION => if this option button is selected, then textbox NOTES must
not be empty (some text must be written). So if it is empty, then message "ENTER NOTES"
Labeling used in subwindow PROPERITIES in first row (Name) in VBA window (alt+F11):
* USERFORM => userform_packing, userform_suppy
* TEXTBOX => txt_date, txt_notes, txt_quantity
* FRAME => frame_description, frame_product
* OPTION BUTTON => DESCRIPTION; opt_sell, opt_supply, opt_gratis, opt_correction, opt_transfer, opt_other//
PRODUCTS; opt_vit, opt_cit, opt_golt, opt_supt, opt_get1, opt_get4, opt_flt, opt_lovt, opt_tuma, opt_taka
* COMMAND BUTTON => cmd_pack, cmd_supply, cmd_exit
I would appreciate any help, if someone could write code or if I could correct assemble code from appropriate written parts of code.
Hope my post is not too long and it is enough clear and concise.
Thanks in advance!