Show message when the value in a column is negative

[Simona]

Dear All,

I've been trying to create an automatic check that will tell me when any value in the column CB turns negative.
I found the following code on the internet but I'm not able to make it work.
Could someone please help me!

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    With Target
        If .Count > 1 Then Exit Sub
            If Not Range("$CB$8:$CB$500").Cells Is Nothing Then
                Application.EnableEvents = False
                If Range("$CB$8:$CB$500").Value < 0 Then
                   MsgBox "Negative Value. Plese correct the amount before continuing."
                End If
                Application.EnableEvents = True
            End If
        End With
    End Sub

Thanks,
Simona

 

[Jonmo1]

Try

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim MyRange As Range, c As Range
Set MyRange = Intersect(Target, Range("CB:CB"))
If MyRange Is Nothing Then Exit Sub
For Each c In MyRange
    If c.Value < 0 Then 
        MsgBox "Negative value was entered in " & c.Address & VbNewLine & "Please correct the amount before continuing"
        Exit For
    End If
Next c
End Sub

Hope that helps.

 

[Simona]

Hi Jonmo1,

thank you for your prompt response!!!
Your code works fine if I enter manually a negative amount in the column CB but it doesn't work if in the column CB I have formulas instead.
I have one amount in the column A and then a few deductions in the other columns (between B and CA).
As the total of deductions can't be higher than the original value in column A I want to create an automatic check.
Is it possible to create a code that checks when the formula returns a negative value?

Thank you again!

 

[Simona]

Dear Jonmo1,

after some researches over the internet I found out that the Worksheet_Change doesn't work with formulas.
I used instead the Worksheet_Calculate changing slightly your code and now everything is working fine.

Here we go:

Private Sub Worksheet_Calculate()
    Dim MyRange As Range, c As Range
    Set MyRange = Range("CB:CB")
    If MyRange Is Nothing Then Exit Sub
    For Each c In MyRange
    If c.Value < 0 Then
        MsgBox "Negative value was entered in " & c.Address & vbNewLine & "Please correct the amount before continuing"
        Exit For
    End If
Next c
End Sub

Thank you for your time,
Simona

 

[Jonmo1]

Glad you got it sorted out...

Another approach when the cells are formulas is...

Instead of using the Calculate event,
Look at what cells your formula refers to, and use the Change event to monitor THOSE cells instead.


I prefer this approach because the Calculate Event triggers every time ANYTHING on the sheet changes. Regardless of which formula updated.

This is also true of the change event, however..
With the Change event, you can restrict your code to run on only if specific cells changed
by using Intersect(target, range("...")).

There is no Target in the Calculate event.
So there is no way to know WHICH formula changed.
So you can't restrict your code to run based on which formula changed...

So if the value of another formula completely unrelated to the one you're monitoring changes, your code still runs needlessly.


So for example,
If your formulas in column CB are
=SUM(A1:B1) 'simple example I know..

Then you would do this..

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim MyRange As Range, c As Range
    Set MyRange = Range("CB:CB")
 
    'This makes the code run only if the cells your formula refer to are changed.
    If Intersect(Target, Range("A:B")) Is Nothing Then Exit Sub
 
    For Each c In MyRange
    If c.Value < 0 Then
        MsgBox "Negative value was entered in " & c.Address & vbNewLine & "Please correct the amount before continuing"
        Exit For
    End If
Next c
End Sub

Hope that helps.