Excel Negative Value Warning

[bmpreston]

Hello,

I'm working on a primitive inventory sheet and came across this:

https://www.mrexcel.com/forum/excel-questions/141057-negative-value-pop-up-warning.html

From here:

Private Sub Worksheet_Calculate()
For Each cell In Range("B5:B16")
If cell.Value < 0 Then MsgBox ("Warning: Negative value!."), , "Invalid Entry"
Next cell
End Sub
This works well, except, if I use a separate worksheet to alter the data, it locks me in a permanent Message Box, even had to Force Quit to get it out. lol.

I need just a single pop up on evaluation.

Any help?

Thanks

 

[GR00007]

You could use validate - it's on the data tab - highlight the column where you want only positive values and select data validation

 

[bmpreston]

I couldn't seem to get that to work. The data is actually a formula and is never directly entered. It is a simple math problem of on hand minus used parts plus new delivery. If the total is less than zero, explain yourself....

Thanks

 

[GR00007]

Here's a tutorial on validation: https://www.excel-easy.com/basics/data-validation.html
Since you're using a formula to create the field, you could put another layer in it:

=IF([B]your current formula[/B] < 0,"Negative not allowed",[B]your current formula[/B])

FYI - I've run into Excel hanging with Worksheet_Calculate(). I wonder if activating the worksheet containing your target range just prior to the For.. Next.. would work.

 

[bmpreston]

That's an option. Hoping for a MSGBOX.

Thanks