Warning if quarterly forecasts exceed annual budget

[K. Georgiadis]

Hi folks,

Attached is a small file that describes what I'm trying to do:

Tab "Original Design" contains code provided by Carlos Paleo and works great. It is a multi-year arrangement of forecasts and, if the user enters quarterly forecasts that exceed the annual budget, a warning message is displayed until the problem is corrected.

Tab "Alternate Design" is a single-year columnar arrangement of the quarterly forecasts. How would I change the code so that I would also get a warning if the quarterly forecasts exceeded the annual budget?

Thanks for your help

[malik641]

Tell me if this is what you were looking for.


Hope this works!!

[Zack Barresse]

Tell me if this is what you were looking for.


Hope this works!!
I don't think your attachment came through..

[malik641]

Sorry...

[K. Georgiadis]

YES! with a slight change to the message box text, this does exactly what I want! Thanks, I'm marking it solved...:beerchug:

[malik641]

Sweeeeet:yes

[K. Georgiadis]

Joseph, one quick (and likely dumb) question:

In your proposed code, what are the following lines designed to do?


If z <> "" Then
y = MsgBox(z, vbOKOnly, "Are you certain?")
End If
End Sub

[malik641]

-Basically at the beginning of the code notice that z = ""
-And i = 2 (this is because your first row of data is in row 2)
-The While procedure loops until i is greater than the amount of rows of data. Each loop it adds 1 to i to check the next row, etc, etc. It also checks each row for a certain criteria (i.e. if the Balance is less than zero). If this is TRUE, it sets the information for the MsgBox. Example:
If Criteria is met,
z = {z("") & blah & blah etc etc}...........z went from nothing to all this information
If Criteria is met again (in the next row),
z = {z("") & blah & blah etc etc} & blah & blah etc etc....Now z has all this additional info

Basically now at the end of the loop z has a value NOT EQUAL to "" (z<>"")
-Therefore making the posted If statement TRUE, and displaying the MsgBox with all the balance Product letters that have a negative balance.

If no criteria was ever met in the first place (i.e. no balance was below zero):
-The posted If statement is FALSE and therefore doesn't do anything (like show the MsgBox).

Hope this answers your (not so dumb) question:thumb

[K. Georgiadis]

I probably should not be tacking on a question to a thread that was already marked "Solved," but what would I do if I also wanted wanted a warning message if the balance on column H is positive, which would mean that the quarterly forecasts are not sufficient to meet the annual budget? In that case I would want to display the message "Your quarterly forecasts cannot satisfy the annual budget for Product (x)"

[malik641]

Check this out :thumb

[K. Georgiadis]

Great!! Double-solved!! :beerchug:

[Zack Barresse]

Great!! Double-solved!! :beerchug:
Great!!! A Two-Fer!!! :D

[K. Georgiadis]

A follow-up question on this (probably falling in the category of nit-picking):

let's say that a case of insufficient forecasts occurs on the first row of the table and that all the remaining rows show quarterly forecasts in excess of the annual budget. With the current code, the warning on the first row will not appear until all the remaining rows have been taken care of. Is there any way to program this so that the warnings are handled independently, in the order in which they occur in the table, or would that involve too many lines of code?

[malik641]

Here ya go!
It doesn't pop up in the order of the table exactly...it goes by the negative balances first...then the balances that has budget money left over. Tell me if this is good for now.
And that wasn't nit-picky...It's more efficient, which it should be.:yes

[K. Georgiadis]

From memory, the code looks the same. Could you point me to the amended line(s) (with apologies for being such a pain!) :o:

[malik641]

From memory, the code looks the same. Could you point me to the amended line(s) (with apologies for being such a pain!) :o:
Nah it's fine :)
It is now:

If z <> "" Then
y = MsgBox(z, vbOKOnly, "Are you certain?")
End If
If x <> "" Then
w = MsgBox(x, vbOKOnly, "Are you certain?")
End If

From:

If z <> "" Then
y = MsgBox(z, vbOKOnly, "Are you certain?")
ElseIf x <> "" Then
w = MsgBox(x, vbOKOnly, "Are you certain?")
End If
This way it checks both z and x for values that do not equal "" and it will now display them both :thumb
Instead of before where if z<>"" then it would only display y (because of the ElseIf)

[K. Georgiadis]

Got it! A three-fer!! thanks

[Zack Barresse]

Got it! A three-fer!! thanks
:rotlaugh:

[malik641]

Got it! A three-fer!! thanksI haven't been to this site long, but I'm pretty sure this is some sort of record (well, documented, at least):beerchug:

[K. Georgiadis]

Headed for the Hall of Fame? :beerchug:

Seriously though, thanks again Malik641

[malik641]

:thumb Anytime! Glad I can help!:thumb