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

 

[Bushman]

Thats it.

S = S + T

 

[Smitty]

I'm not sure if I got the logic right, but see how this goes:

<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:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><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>            <SPAN style="color:#007F00">'   If R<0-->MsgBox -->Otherwise R=Q-S (where S = S + T)</SPAN><br>                Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>                    <SPAN style="color:#007F00">'   Reset R</SPAN><br>                        <SPAN style="color:#00007F">If</SPAN> LenB(Target.Offset(, -2).Value) <> 0 <SPAN style="color:#00007F">Then</SPAN> Target.Offset(, -2).Value = ""<br>                    <br>                    Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>                        <SPAN style="color:#007F00">'   S = S + T(arget)</SPAN><br>                            i = Target.Value + Target.Offset(, -1).Value<br>                            Target.Value = i<br>                        <SPAN style="color:#007F00">'   R = Q - S</SPAN><br>                            Target.Offset(, -2).Value = Target.Offset(, 3) - i<br>                        <SPAN style="color:#007F00">'   Check R for <0 value</SPAN><br>                            <SPAN style="color:#00007F">If</SPAN> Target.Offset(, -2).Value < 0 <SPAN style="color:#00007F">Then</SPAN><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">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>                    Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Bushman]

Thanks for this Smitty, the R=Q-S bit returns a negative T value. i.e. if 2 is entered into T then R reads -2, if 5 is entered into T then R reads -5, etc but the rest I think is working. I'll try to work it out.
If only I could speak VBA!
Thanks again.

 

[Smitty]

Then you just need to switch the offsets around a bit.

For help, if Target = T, then Target.Offset(,-1) = S, (,-2) = R, (,-3) = Q, etc. Lose the minus sign and you go forward --> .Offset(,1) = U.

Note the comma in there. Offset takes two arguments (Row,Column) - If you're not doing anything with Row, which you're not in this case, you can leave it out (and save a modicum of processing time), but you need the comma to tell VBA which argument you want.

 

[Bushman]

Brilliant! Thanks again Smitty, thats great, I'll have a go in the morning when my brain works best.

 

[Bushman]

Brilliant, thanks again Smitty. I amended your offsets and to get exactly what is required!!!!!! I learned so much. Nice one.

i = Target.Value + Target.Offset(, -1).Value
' R = Q - S
Target.Offset(, -2).Value = Target.Offset(, -3) - Target.Offset(, -1)

 

[Bushman]

Re: Expanding to a range

Can anyone show me how to expand this code to work for a range of cells? T10:T55

If Target.Address = "$T$10" Then
Range("S10").Value = Range("S10").Value + _
Range("T10").Value
End If

At the mo its running ok but I have to write this if statement for each line which cant be right.

 

[wigi]

Re: Expanding to a range

Look back at my suggestion, post # 3 in earlier in the topic.

 

[Bushman]

Re: Expanding to a range

Thx Wigi, I will do that, srry I cant see what I already have and I dont know enough to combine statements, I'll see how I get on. Thanks again!

 

[Bushman]

Re: Expanding to a range

No, still dont have it, here's what I have so far thx to Smitty:

Private Sub Worksheet_Change(ByVal Target As Range)
' Code goes in the Worksheet specific module
Dim rng As Range
Dim i As Long
' Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")
Set rng = Target.Parent.Range("L10:L55")
' Only look at single cell changes
If Target.Count > 1 Then Exit Sub
' Only look at that range
If Intersect(Target, rng) Is Nothing Then Exit Sub
' Action if Condition(s) are met (do your thing here...)
' Which I post in for every row...
If Target.Address = "$L$10" Then
Range("K10").Value = Range("K10").Value + _
Range("L10").Value
End If
If Target.Address = "$L$11" Then
Range("K11").Value = Range("K11").Value + _
Range("L11").Value
End If
' If R<0-->MsgBox -->Otherwise R=Q-S (where S = S + T)
Application.EnableEvents = False
' Reset R
If LenB(Target.Offset(, -2).Value) <> 0 Then Target.Offset(, -2).Value = ""
 
Application.EnableEvents = False
' S = S + T(arget)
i = Target.Value + Target.Offset(, -1).Value
' R = Q - S
Target.Offset(, -2).Value = Target.Offset(, -3) - Target.Offset(, -1)
' Check R for <0 value
If Target.Offset(, -2).Value < 0 Then
MsgBox "Check Item", vbCritical + vbOKOnly, "Out Of Stock!"
With Target
.Select
.Value = ""
End With
End If
If Target.Offset(, -2).Value < 1 Then
MsgBox "Re Order This Item", vbCritical + vbOKOnly, "Out Of Stock!"
With Target
.Select
.Value = ""
End With
End If
Application.EnableEvents = True
End Sub

I have tried to seet the ranges but am having no joy.