Inventory Stock Management

[Isaac Fiifi Ferguson]

Please I seriously need a VBA code for my inventory user form.
This code should avoid negative stock balance. It should prompt and avoid me from making sales more than available in stock.
Below will help your understanding to my request.

(A)	Product Name	Opening Stock	Purchase	Sales	Closing Stock
	Apple	85​	0​	100​	-15​
	Watermelon	26​	0​	15​	11​
	Sugarcane	13​	0​	28​	-15​
	Banana	50​	0​	5​	45​
	Pawpaw	77​	0​	80​	-3​

 

[awoohaw]

why use VBA? Data Validation will give input error messages:

mr excel questions 22.xlsm
	A	B	C	D	E	F
1	(A)	Product Name	Opening Stock	Purchase	Sales	Closing Stock
2		Apple	85	0	100	-15
3		Watermelon	26	0	15	11
4		Sugarcane	13	0	28	-15
5		Banana	50	0	5	45
6		Pawpaw	77	0	80	-3
Isaac Fiifi Fergusson

Cells with Data Validation
Cell	Allow	Criteria
E2:E6	Whole number	<=C2

 

[awoohaw]

why use VBA? Data Validation will give input error messages:

mr excel questions 22.xlsm
	A	B	C	D	E	F
1	(A)	Product Name	Opening Stock	Purchase	Sales	Closing Stock
2		Apple	85	0	100	-15
3		Watermelon	26	0	15	11
4		Sugarcane	13	0	28	-15
5		Banana	50	0	5	45
6		Pawpaw	77	0	80	-3
Isaac Fiifi Fergusson

Cells with Data Validation
Cell	Allow	Criteria
E2:E6	Whole number	<=C2

NOTE: The above is the cells before the data validation was put on, so it still has the values which cause negative differences. If you use this validation it will not allow you to type 100 in cell E2.

 

[Isaac Fiifi Ferguson]

why use VBA? Data Validation will give input error messages:

Thank you so much. That's very straight forward and very supportive. Am grateful

 

[awoohaw]

you are welcome It does have it limitations however.
Someone can paste values into the cell versus typing them and the data validation will not kick in.