Picky Boss! Need code snippet for color, please...

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Angyl - 05 May 2006 17:11 GMT

After sweating and slaving to hack out a custom form in Word2003 and a
UserForm on top of it, my boss is not satisfied with the simple calculation
field that shows a difference between two amounts as DUE $12.34 or OWED
($12.34)

HE WANTS IT COLOR CODED! Red for amount owed and Green if money is due us.

*sigh*

Anyone know how best to code something like that? Is it even possible? (I'd
be happy to go back and tell him it can't be done).

I figure it could work as either a part of the VB code in the userform
command button that inputs the data (check that box and color appropriately)
or as a Macro that runs automatically when the userform is closed, or it
could be coded into the form field itself.

I know specifics are appreciated with these types of questions so the three
form fields I'm working with are bookmarked:
"Total"
"TotalPaid"
"OweDue"

Thanks in advance for your help!

Reply to this Message

Greg Maxey - 05 May 2006 17:37 GMT

If you are using formfields, the you can use a formatting switch:

E.g., { (=5+5) \# "#.00;(#.00);0" }

Format tthe first #.00 green, the (#.00) red and the 0 black

Reply to this Message

Angyl - 09 May 2006 18:32 GMT

I'm sorry, Greg, you lost me. I have one bookmarked field (TotalPremiumDue)
and a second one (TotalCollected). Right now what I have is a third field
that runs this calculation:
=(TotalCollected)-(TotalPremiumDue)

It gets the job done, but after kicking around your formula below, I can't
seem to apply it properly, so that if the number is ($0.00) negative, it is
red and $0.00 positive, green.

Thanks for all your help!

> If you are using formfields, the you can use a formatting switch:
>
> E.g., { (=5+5) \# "#.00;(#.00);0" }
>
> Format tthe first #.00 green, the (#.00) red and the 0 black

Reply to this Message

Dave Lett - 09 May 2006 18:42 GMT

Angyl,

Greg's suggestion had a misplaced open parens.
Try this:
=(5+5) \# "#.00;(#.00);0"
=(5-10) \# "#.00;(#.00);0"

Copy this to a document. Hightlight the first sample and press CTRL + F9 (to
create a field) and then press F9 (to update the field).
Do the same for the second sample.
Turn on reveal field codes.
In each sample, highlight the first "#.00" and change the font color to
green.
In each sample, highlight "(#.00)" and change the font color to red.
Put the cursor in each sample, press F9, and then turn off reveal field
codes.

HTH,
Dave

> I'm sorry, Greg, you lost me. I have one bookmarked field
> (TotalPremiumDue)
[quoted text clipped - 14 lines]
>>
>> Format tthe first #.00 green, the (#.00) red and the 0 black

Reply to this Message

Angyl - 09 May 2006 19:08 GMT

Works! Thanks, Dave!

> Angyl,
>
[quoted text clipped - 34 lines]
> >>
> >> Format tthe first #.00 green, the (#.00) red and the 0 black

Reply to this Message

Greg Maxey - 09 May 2006 19:19 GMT

Here again don't use a calculation field. You a straight formula field
with Ref fields to the bookmarks:

{ = { Ref Text1 }+{ Ref Text2 } \# "#.00;-#.00;0"}

Replace Text1 adn Text2 with your bookmark names. Format the -#.00
with red font.

You will need to set 0 as the default value for the formfields to mask
a syntax error if either is blank.

Reply to this Message

Angyl - 09 May 2006 20:53 GMT

You're the Best, Greg!

> Here again don't use a calculation field. You a straight formula field
> with Ref fields to the bookmarks:
[quoted text clipped - 6 lines]
> You will need to set 0 as the default value for the formfields to mask
> a syntax error if either is blank.

Reply to this Message

Rate this thread: