Drop down box to change stock amount and email sending

Hi all,

I'm wondering if anyone can help. I have a number of stock items.

Beside each of them, there is a drop two choices "in stock" and "checked out".

For each stock item i will also have a cell which shows the quanitiy in stock.

What i want is to allow someone to take an item from my stock store and in the excel worksheet, I want them to be able to choose "checked out" from the dropdown box, which will then decrease the stock. When the stock reaches a certain value, a prompt in the form of a message box will appear to email a specific address to ask someone to reorder more.
So my main two points are :
(1) How to decrease/increase the value if "checked out" is selected or if "in stock" is selected.

(2) How to have a message box pop up which will link to opening the email outlook client in order to send an email to notify that new stock is required?

I have excel 2007.

any help would be appreciated.

Thank you.

Hi Hulpeloos! thanks for the reply! Unfortunately i can't open the attached doc??? seems to be an issue with it.
thanks.

Hi Hulpelos,

This is awesome. I am lookin for something slightly different but it will be a great help. Basically for each project there would be a range of serial numbers read from another sheet. so if you can imagine, selecting a product, it comes up with a listing of serial numbers in a message box or list, then you could select the serials to move to a corresponding box which woudld decrease the stock. would this be something difficult to do ?

Hi Hulpeloos. Let me explain better. Say we have 5 products. Of each of these products I will have a worksheet. In the worksheet it will hold serial numbers for each component of the product. so 5 worksheets, on each work sheet a number of components. From the visual basic aspect, we would have a dropdown list as you have shown me. On the dropdown list there are the 5 products listed. When i highlight one product, in a Listbox below that, it will show me the components listed for the that product. I can then click on 1 (or more) components and then click on "checked out" and click save. This will remove the component from the listbox and the worksheet. This is what im looking for. If it's possible i'd like to components through a text box, or i could just instruct to manually add them through the worksheet so they will show up in the listbox again when the product is selected. If you can help me this i would really appreciate it. I'm not the greatest programmer! thank you!