Inventory managment system (stock level)


New Member
Nov 19, 2018

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
- 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!





Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand


New Member
Nov 19, 2018
PS - one another thing for code:
* clear all data in textbox or option buttons after making transfer to REGISTRY sheet => so after clicking command button
PACK(OUT) or SUPPLY(IN) everything resets (clears or turns back to default option/value)


MrExcel MVP, Moderator
Jun 12, 2014
Office Version
  1. 365
  1. Windows
Cross posted

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Also you have replied to your post 3 times within the space of an hour, this makes it look as though somebody is helping you, so any potential helpers may not bother looking at your thread.


New Member
Nov 19, 2018
Sorry for mistakes, but I did not find way how to edit post. So I attached pics in following posts. But I am unabe to attach xlms file... so here are two links (hope it will work one of them)


I have also one question about transfering data from userform to sheet (table):
  • is it possible to program date textbox (txt_date) to transfer two data into two horizontal (neighboring) cells (columns)?
For example:
  1. in first cell (column) date and second cell (column) hour/minute/second? Additional column, can be named as TIME.
I would need this for another sheet, where i would create table for monthly sum of packed products.


Watch MrExcel Video

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back