Error alert if a certain limit is exceeded

[K. Georgiadis]

Hi Folks,

I attached a small Excel file to illustrate what I am trying to do. I have annual forecasts for four products to be broken down by quarter. The user will hard code the quarterly numbers the total of which must equal the annual forecast. On rows 9, 18, 27 and 36 I have provided "balance" cells so that the user can keep track of what amounts still need to be allocated.

In cell B9 I show a hypothetical case where the quarterly totals exceed the annual budget. What I want to do is to have a warning message box appear if the number in any of the cells in rows 9, 18, 27, and 36 is less than zero, with the message "your quarterly amounts exceed the annual forecast," and an "OK" button which must be pressed before the user can continue.

Can someone help me with that?

[Paleo]

Hi K. Georgiadis,

press Alt + F11 and double-click sheet1, then copy this code and paste in it:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Lin As Variant, i As Integer, Forec As Variant, Ano As String
Lin = Array(9, 18, 27, 36)
Forec = Array("B", "C", "D", "E", "F", "G")
For Each linha In Lin
For Each col In Forec
If Range(col & linha) < 0 Then
Ano = Range(col & linha - 6)
z = MsgBox("Your quarterly amounts exceed the " & _
"annual forecast for the year of " & Ano & ".", vbOKOnly)
End If
Next
Next
End Sub

This will do what you want.

[K. Georgiadis]

Thank you. I'll give it a try

[Paleo]

Ok, and if anything goes wrong just post it here...

[K. Georgiadis]

I tried it but there is a problem: after the warning message appears, I place the cursor on the offending cell to correct the number. As soon as I place the cursor on the cell, the warning message appears again before I get a chance to type a new number

[Paleo]

Hi,

you may modify the Sub from

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

to

Private Sub Worksheet_Change(ByVal Target As Range)

,

this will make it to only check when you change a cell value.

Is this better for you?

[K. Georgiadis]

yes this works fine. Thanks! I'm marking it solved

[Paleo]

Great,

if you need help again, just come back.

[K. Georgiadis]

Thanks again.

Let the site administrator/approver take note: I have been visiting this site since June 2004. It is one of the finest, if not the finest, for getting prompt and expert advice. Kudos!

[Zack Barresse]

Thank you very much! That is very kind of you.

We all do what we can, and take pride in our work. We are quite fortunate to have some of the worlds finest coders! A special thanks to Paleo for such an outstanding job and prevelant commitment!!

Thanks again.

Let the site administrator/approver take note: I have been visiting this site since June 2004. It is one of the finest, if not the finest, for getting prompt and expert advice. Kudos!

[Paleo]

Thank you very much Zack .

But hey remember I have the best master that are out there.

I have learned a lot from you, Jake and John.

[K. Georgiadis]

What have I started?
But, really guys, when I come to this site with a question, I know that I'm going to find an answer. Don't be surprised if you see me again soon...

[Paleo]

Get back whenever you want we will all be wanting to help you out.

[K. Georgiadis]

Don't you worry. I'm posting a new request separately right now!

[Paleo]

Great and I am going there to check it out... Hope I may help you out.