Inventory managment system (stock level)

[topnikd]

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"
* 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)

PS: if possible transfer date in two columns; one column just date (dd/mm/yyyy) and other just time (hh/nn/ss). Additional column,
can be named as TIME.

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!

CROSS POSTING:

mrexcel.com/forum/excel-questions/1078111-inventory-managment-system-stock-level.html
ozgrid.com/forum/forum/help-forums/excel-vba-macros/1211723-inventory-management-system-stock-level

[LeoTaxi]

nice pictures

[topnikd]

nice pictures

Thank you. You are forum expert, so you could solve code, which is to heavy for me

[LeoTaxi]

most of us need excel files no pictures

Cheers
Leo

[jeffreybrown]

Your post does not comply with Rule 3 of our Forum RULES.

Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

Please make sure to include the full link, so that other members will be permitted to continue helping you

Note that if you have fewer than 10 posts, you may need to type the link, or past it in pieces (or ask a mod to do that for you)

[topnikd]

Sorry for mistakes. I updated my post.

[LeoTaxi]

for start


kind regards
Leo

[topnikd]

Thank you. I must admit your code is much more sofisticated than mine, so I need time to understand it (i used for example a lot of "offset" lines... so it is longer and not so versatile).

Some requests:
* instead of typing date please NOW function (if possible into two columns - one date, second time)
* not disappearing userform after making entry (massage box: succesfull entry)
* default values for quantity and description (-1 and sell/supply)

Disclaimer: I am not from Netherlands, I just use VPN. But thanks for yours attention. So please write code/everything ( i see code word "vervolg") in english (I will then easier translate to my lanugage).

And my MS office version is 2016, if this data is important.

[dotchiejack]

Example with your requests
@LeoTaxi nice code.

[LeoTaxi]

with modifications as asked


Kind regards
Leo

[jeffreybrown]

Disclaimer: I am not from Netherlands, I just use VPN. But thanks for yours attention. So please write code/everything ( i see code word "vervolg") in english (I will then easier translate to my language).

And my MS office version is 2016, if this data is important.

Maybe you can do two things:
1) Update your profile to the version you are using. It can make a big difference in the techniques used/offered
2) Remove Netherlands as your location so language is not an issue. If you don't want people to know where you are, just leave it blank