If a cell value is less than 0 message box

[Bushman]

Hi,
Just been asked to help and VBA is new to me, can you help?
I'm running this sub as a tally which works ok

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$T$10" Then
Range("S10").Value = Range("S10").Value + _
Range("T10").Value
End If
End Sub

I need help with the following...
1.If the value is less than zero I would like it to alert with a message box.
2.Can this be done for a range of cells (T10-T55)?
3.Can 1. be done without asking it to run, i.e. automatically?

Please help as I didnt know VBA existed until Thursday and it's a steep curve! Thanks

 

[Norie]

1 What value is below 0?

2 Yes.

3 That code should run automatically anyway, it would be tricky to manually run it.

For 2 I assume the calculations would all be based on the same row, the one where the value has been changed.

For 3 apart from a messagebox what do you want to happen?

 

[wigi]

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count <> 1 Then Exit Sub
    If Not Intersect(Target, Range("T10:T55")) Is Nothing Then
        If Target.Value < 0 Then
             MsgBox "Negative number"
        Else
             Target.Offset(,-1).Value = Target.Offset(,-1).Value + Target.Value
        End If
    End If
End Sub

 

[Smitty]

Welcome to the Board!

You're doing pretty well for just picking it up.

This will expand the range and add your message box (I used a bit of an expanded method that takes a few factors into account and is somewhat more stable):

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#007F00">'   Code goes in the Worksheet specific module</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br>        <SPAN style="color:#007F00">'   Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> rng = Target.Parent.Range("T10:T55")<br>             <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>            <SPAN style="color:#007F00">'   Only look at that range</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>            <SPAN style="color:#007F00">'   Action if Condition(s) are met (do your thing here...)</SPAN><br>                Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>                    <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> Target.Value<br>                        <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Is</SPAN> < 0<br>                            MsgBox "You need to have a value greater than 0!", vbCritical + vbOKOnly, "Value Too Low"<br>                            <SPAN style="color:#00007F">With</SPAN> Target<br>                                .Select<br>                                .Value = ""<br>                            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>                        <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Else</SPAN><br>                            Target.Offset(, -1).Value = Target.Offset(, -1).Value + Target.Value<br>                    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN><br>                Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

And a Change event will fire automatically. Note that if T10:T55 are changing as the result of a formula, you'll need a calcuate event.

Hope that helps,

 

[Bushman]

Thx Smitty, that is fantastic, It works great! Though I dont understand it!!!!
I have realised I am an idiot and confused myself and what I asked for wasnt what I need. I hope you are willing to help such a Klutz.
The tally (my bit) is subtracted from a set positive number value (in Q10-Q55) with a simple sum, it is this value (in R10-R55) that I needed the alert for!
if you cant help then thanks very much for that first macro it did exactly what I asked!

 

[Bushman]

Thx Wigi and Nori too for replying to my post, apologies to you both for not asking the right question and wasting your time. Sin

 

[Norie]

You didn't ask the wrong questions, perhaps you didn't give the exact information but the way this has fallen in your lap can excuse that I think.

Mind you, perhaps it's just me, but it's still not quite clear what value(s) you want to work with/check.

 

[Smitty]

Glad it helped. Now on to the other...

The tally (my bit) is subtracted from a set positive number value (in Q10-Q55) with a simple sum, it is this value (in R10-R55) that I needed the alert for!

I'm not sure if I get this part? Should this be a stand-alone thing or is it in conjunction with the first code?

Maybe you can put it in a broader scope like:

"I enter values in T10:T55 - I need to add that number to the value already in S10:S55. Then in Q10:Q55 there's a number that...And in R10:R55 there's another number that..."

You need to show the relationship between Q, R, S, & T and point out which cells receive data manually and which are formulas.

 

[Bushman]

To be clear, apologies for not having been so thus far, values are entered manually into column T, range10:55.

Then this macro...
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$T$10" Then
Range("S10").Value = Range("S10").Value + _
Range("T10").Value
End If
End Sub
...has been adding consecutive entries into column S.
This value is subtracted from a fixed manually entered positive numerical value in column Q, this is expressed in column R.
If the value in column R is negative a message box should alert that an error has occured.
Thanks once again, i hope this is clearer.

 

[Smitty]

So essentially:

If R<0-->MsgBox -->Otherwise R=Q-S (where S=T)? Or is it S = S + T?