PDA

View Full Version : [SOLVED] Error alert if a certain limit is exceeded



K. Georgiadis
02-16-2005, 08:40 PM
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
02-16-2005, 09:11 PM
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
02-16-2005, 09:22 PM
Thank you. I'll give it a try

Paleo
02-16-2005, 09:24 PM
Ok, and if anything goes wrong just post it here...

K. Georgiadis
02-16-2005, 09:26 PM
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
02-16-2005, 09:34 PM
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
02-16-2005, 10:33 PM
yes this works fine. Thanks! I'm marking it solved

Paleo
02-17-2005, 08:35 AM
Great,

if you need help again, just come back.

K. Georgiadis
02-17-2005, 08:44 AM
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
02-17-2005, 11:34 AM
Thank you very much! That is very kind of you. :yes

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!! :clap:


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
02-18-2005, 06:35 PM
Thank you very much Zack :bow: :bow: .

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

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

K. Georgiadis
02-18-2005, 07:06 PM
What have I started?:yes
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
02-18-2005, 07:12 PM
Get back whenever you want we will all be wanting to help you out.

K. Georgiadis
02-18-2005, 08:38 PM
Don't you worry. I'm posting a new request separately right now!

Paleo
02-18-2005, 08:55 PM
Great and I am going there to check it out... Hope I may help you out.