If Then Mathmatical - syntax and/or VBA

  • Thread starter Thread starter John
  • Start date Start date
J

John

I have a simple form tracking progress on projects each month.
There is a textbox for the Planned % complete ("tbxmonthplan"), a second
text box forthe Actual % complete ("tbxmonthact"), and a third textbox for a
monthly indicator ("tbxmonthInd") based on a comparison of the two previous
text boxes.

If tbxmonthact is less than 95% of tbxmonthplan (e.g a 5% variance from
target progress) I want tbxmonthInd to indicate "Y" and format tbxmonthInd
background yellow. Further, If tbxmonthact is less than 80% of tbxmonthplan
(e.g a 20% variance from target progress) I want tbxmonthInd to indicate "R"
and format tbxmonthInd background red. If they are above 95% on target I
want to indicate "G" and format background as green.

What is the syntax for this or does it require VBA?? Currently the control
source for tbxMonthInd is a field in a table which I know is a no-no for
calculated fields.

Help is appreciated.

qwerty
 
John,

Try something like the following.

Private Sub tbxmonthact_AfterUpdate()
Dim dblPercentage As Double
dblPercentage = (Me.tbxmonthplan.Value / Me.tbxmonthact.Value) * 100
Select Case dblPercentage
Case 80 To 95
Me.tbxmonthInd.Value = "Y"
Me.tbxmonthInd.ForeColor = vbYellow
Case Is < 80
Me.tbxmonthInd.Value = "R"
Me.tbxmonthInd.ForeColor = vbRed
Case Is >= 95
Me.tbxmonthInd.Value = "G"
Me.tbxmonthInd.ForeColor = vbGreen
End Select
End Sub

Let me know if this helps.

Ken Warthen
(e-mail address removed)
 
Ken - This works. You did the hard part. Thanks a ton.

I did have to reverse the math as it was penalizing (red) over-achievement
and rewarding (green) non-achievement.

Corrected line:
dblPercentage = ( Me.tbxmonthact.Value / Me.tbxmonthplan.Value ) * 100
 
Ken - Is there any way for the form to hold the formatting after it is
closed? Currently I am using conditional formatting but it i can keep it in
VBA that would be great.
 
Use the same code in the form's Current event instead of
tbxmonthact_AfterUpdate. That way, it will change with each record (in
single form view).
 
Thanks Arvin

Now the issue arises that future months show red b/c the math indicates
<80% to target. Is there code to make tbxmonthInd = "Y" say "N/A" when
tbxmonthact is null?
 
Back
Top