Changing Color after Update

[Sharon]

How can I apply this code to all the field names that end
in "excpt"? Can you use a wildcard? *excpt? Or do you
have to write the code for every field?

Also, is there cleaner way to write this code?

-----------------------------------------

Private Sub prncpl_bal_excpt_AfterUpdate()

If Me.prncpl_bal_excpt <> "0" Then
Me.prncpl_bal_excpt.ForeColor = vbRed
Me.prncpl_bal_excpt.BackColor = vbYellow
Else
Me.prncpl_bal_excpt.ForeColor = vbBlack
Me.prncpl_bal_excpt.BackColor = vbWhite

End If

 

[Sandra Daigle]

There are a couple of ways to do this - one is to select all of the relevant
controls then in the AfterUpdate event put

=togglecolor()

Then in the form's class module put the following function:

Private Function ToggleColor()
With Me.ActiveControl
If Right(.Name, 6) = "_excpt" Then
If .Value <> 0 Then
.ForeColor = vbRed
.BackColor = vbYellow
Else
.ForeColor = vbBlack
.BackColor = vbWhite
End If
End If
End With
End Function

If you only put the function call into the AfterUpdate event of the
relevlant controls (those ending with "_excpt" then you don't really need
the test on the name).

 

[Sandra Daigle]

One other thought - you might want to do this using Conditional Formatting -

To use, select the relevant controls, click Format, Conditional Formatting.
Check the help for more details.

CF is somewhat limited in what it can do but it is useful for many
situations. Stephen Lebans has put together a sample database demonstrating
some ways to use Conditional Formatting:
http://www.lebans.com/conditionalformatting.htm

 

[Jonathan Parminter]

-----Original Message-----
How can I apply this code to all the field names that end
in "excpt"? Can you use a wildcard? *excpt? Or do you
have to write the code for every field?

Also, is there cleaner way to write this code?

-----------------------------------------

Private Sub prncpl_bal_excpt_AfterUpdate()

If Me.prncpl_bal_excpt <> "0" Then
Me.prncpl_bal_excpt.ForeColor = vbRed
Me.prncpl_bal_excpt.BackColor = vbYellow
Else
Me.prncpl_bal_excpt.ForeColor = vbBlack
Me.prncpl_bal_excpt.BackColor = vbWhite

End If
.

Hi Sharon,
try using a for... next loop

dim ctl as control

for each ctl in controls
if right(lcase(ctl.name),5)="excpt then

' your if statement here
If ctl.value <> 0 Then
ctl.ForeColor = vbRed
ctl.BackColor = vbYellow
Else
ctl.ForeColor = vbBlack
ctl.BackColor = vbWhite
End If

end if
' allow other window events then loop
doevents
next ctl

Luck
Jonathan