VBA code to run message box based on cell in column < = 1

[Spartan300]

Hi,

I have a stock sheet, and on workbook open I would like a message box to run warning the user of the items with 1 or less in stock.

I am unsure how I can reference a column and then pull in the names of the items that correspond to the 1 or less in stock.

Thanks for your help.

 

[Smitty]

You might be better off using Conditional Formatting to indicate the level. If you loop through all of your items, your users will get real tired of who knows how many MsgBoxes popping up.

But assuming that your stock level is in column B and the item name in column A, then this will work:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_Open()<br>    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range<br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Sheets("Sheet1").Range([B2], Cells(Rows.Count, "B").End(xlUp))<br>            <SPAN style="color:#00007F">If</SPAN> c.Value < 1 <SPAN style="color:#00007F">Then</SPAN> MsgBox c.Offset(, -1) & " is low on stock, please reorder.", vbCritical + vbOKOnly, "Reorder Stock"<br>        <SPAN style="color:#00007F">Next</SPAN> c<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

 

[Spartan300]

Hi Smitty,

Works a treat, out of interest my stock is on Sheet2, when I replaced this in the code it had the 'Runtime error '9': Subscript out of range' I replaced this with the tab name 'Stock' and it works fine. Does the tab name become the definitive reference rather than its sheet number?

Thanks very much.

 

[Smitty]

Yes, unless you use the sheet's index number you need to refer to it explicitly by name.

 

[lenze]

As Smitty says, you can use the Index number

 For Each c In Sheet2.Range([B2], Cells(Rows.Count, "B").End(xlUp))

The advantage is, that if someone decides to rename the sheet, the code will still run
lenze