Low Stock Pop-Up Alert

[R1_Canuck]

Hi All,

I'm trying to create a "message box" or "pop out window" to alert a user when the stock is low on the spreadsheet? I have already put in place the conditional formatting for individual cells to turn red etc etc, but I cant find any VBA help regarding the pop up alert.

All it needs to read is "Stock is Low - Please Order/Check Stock" and for there to be an "OK" only option. It would be ideal if, on pressing OK, it would send an email to our engineer as a reminder. Also it is in cell range C10 to C1000.

Any help is appreciated

Many thanks,
Henry

[joe31623]

It can be done rather easily by displaying a Msgbox("message goes here") based on a conditional target range; however, I believe the details you've provided would be a bit sparse to provide you code that would work as required without an example spreadsheet. An Input box that would trigger an additional event could be adapted to your parameters as well.

Can you upload a workbook that I would write code for? Note that I or anyone undertaking this project would require two sets of cells: C10 to C1000 (stock values) and an additional range of cells that defines "too low" or a range with initial values and a fixed (or variable) percentage the stock can drop.

If you provide both an example workbook and the required info. to validate "too low", I can make some progress for you.

[R1_Canuck]

Hi Joe,


Thanks for the response. Sorry, I should have put some more info. I cant attach the actual document but I have made a small example of what it is like - be it fake data and less items.

(Anything under 1 triggers red font on the conditional formatting).

Thanks,
Henry

[joe31623]

So you want a message box to display an alert whenever the column C value (of an occupied row of data, from column A) is below a certain threshhold (using this example, 1) and you'll adapt from there?

I can then call an empty subroutine (with the row of data from which you can extract stock information as an argument of datatype: Range) that you can develop which would send an e-mail using Outlook. I don't have Outlook so I can't debug a subroutine for you, but I can point you towards a very thorough resource a guru on this forum developed: http://www.snb-vba.eu/VBA_Outlook_external_en.html#L112

I was resolving some malware issues earlier today so I only popped on here for a moment, but I expect I'll write you a quick program to do what I mentioned above.

[R1_Canuck]

Yes that is exactly what I want... If you really would do the formula or VBA for this I would appreciate it massively.

[wayneg]

Hi, is this what you are after

[R1_Canuck]

Hi Wayneg,

That is pretty damn close to what I've wanted to create, I'm going to have a play with your VBA in my spreadsheet and see how it goes...

Thanks !!!

[upul13]

Hi
can i have this with more range with cell value,i can add more item, i try to add this for another excel sheet it didn't work. i don't know much about this

[upul13]

Hi wayneg

can you add working indexmatch with email vbn and formula to below what i mentioned-

cell b col 2 cell c col 2 cell d col 2 cell e col 2 cell f col 2 cell g col 2 cell h col 2 cell i col 2 cell j col 2
REORDER (auto-fill) ITEM NO DATE OF LAST ORDER ITEM NAME DESCRIPTION Minimum Quantity Stock REORDER LEVEL Maximum Quantity Stock ITEM REORDER QUANTITY


thanx lot

upul

[remlin]

Hello upul,

Did you solve the the problem? I am also facing the same problem right now.