PDA

View Full Version : Solved: Low inventory alert



ndendrinos
08-16-2009, 11:46 AM
Range C2:L2 house formulas
What will the formula/code be to pop up a message advising that stock is below 10 in any given product?

Joe4 gives a solution for cell that do not house formulas (see pick in attachment)

Thank you

mdmackillop
08-16-2009, 11:54 AM
Referring back to your previous posts, you should carry out the check in the code that sends values to the Stock sheet. Can you post a sample which includes that code.

ndendrinos
08-16-2009, 12:11 PM
Thank you mdmackillop,
Here the file requested , I did not have the time to incorporate the new codes provided by xld in previous post "add to existing value by matching"

My thought is to leave xld's code as is and Call the "To Stock" code at the end ... or run it "on close"

Of course I will take your point of view over any on mine anytime.

ndendrinos
08-16-2009, 01:03 PM
small step towards solution ?
Type Qty = 55 and choose apples
code works

What needs to be addressed is:
Instead of just "C2" in "macro "stock" the range s/b C2:L2
if this is possible it could be something like : For each cel in range C2:L2 If ......

And instead of Calling "stock" it s/b part of "macro "test") ... tried that one & get the excruciating IF error ...

Bob Phillips
08-16-2009, 02:57 PM
Personally, I have no idea what you want to happen, I could not understand your explanation.

ndendrinos
08-16-2009, 03:38 PM
Hello xld,
The point is to alert the user that the inventory of any given "produce" is getting low (less than 50).
A popup should appear . That I just figured out in this attachment BUT what would be even better is if the popup identified the said "produce" OR "produces (more than one)
In this example the popup should say" Stock is low ... reoder prunes soon"

Is this doable and is it doable if several "produces" are in need of reodering?
example (both apples and prunes are less than 50) : the popup would say" Stock is low ... reoder prunes, apples soon"

ndendrinos
08-16-2009, 04:56 PM
some more progress here and nearing the final desired outcome.... if impossible then what I have will do.

In this attachment since both "watermelon AND kiwi" are both low I get the popup twice (because of the loop once for each produce)... that I understand.

Is there a way to see the popup just once alerting me to reoder both produce?

mdmackillop
08-17-2009, 12:15 AM
Sub stock()
Dim c As Range, msg as String
For Each c In Range("C2:L2")
If c.Value < 50 Then
msg = msg & c.Offset(1).Value & vbCr
End If
Next c
MsgBox "Stock is low ... reorder soon" & vbCr & msg
End Sub

ndendrinos
08-17-2009, 05:58 AM
Never thought it possible, Thank you mdmackillop
If you see this or anyone else could you please explain what
vbCr stand for?

Bob Phillips
08-17-2009, 06:38 AM
Carriage return, a built-in VBA constant for that value.

ndendrinos
08-17-2009, 07:26 AM
thank you xld