Changing Color after Update

  • Thread starter Thread starter Sharon
  • Start date Start date
S

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
 
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).
 
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
 
-----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
.
Click to expand...

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
 
Back
Top