Code for conditional formatting

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Need code help! On a form filled with data generated from a query, I need to
format the textboxes based on the data in one of the fields. Conditional
formatting works great, but apparently the pastels aren’t pastel enough so I
have to use custom colors. Here’s the code:

Private Sub Form_Current()
If Me.txtGroup = "A" Then
Me.txtID.BackColor = 12122104
ElseIf Me.txtGroup = "B" Then
Me.txtID.BackColor = 15979518
ElseIf Me.txtGroup = "C" Then
Me.txtID.BackColor = 16776652
ElseIf Me.txtGroup = "D" Then
Me.txtID.BackColor = 16628703

End If

End Sub

The view is “continuous forms†so it shows all the rows of data returned by
the query. The above code is making all of the rows change color depending
on where the cursor is. If the row has a “Bâ€, then all the rows turn pink
(15979518). How do I make the rows show their respective color regardless of
where the cursor is?
 
Use conditional formatting. It is in the general form toolbar, under Format.
Click on the detail part of the form, before accessing the toolbar, to have
the Conditional formatting enabled.

Hoping it may help,
Vanderghast, Access MVP
 
Thanks but I've been asked to use "paler" colors. I don't think I can add
colors to the list in conditional formatting.
 
from the UI, true, but from VBA, false, since in VBA, you more freedom.
From the help file, as example:

----------------
With forms("forms1").Controls("Textbox1").FormatConditions(1)
.BackColor = RGB(255,255,255)
.FontBold = True
.ForeColor = RGB(255,0,0)
End With
-----------------


Hoping it may help,
Vanderghast, Access MVP
 
you can't use code to set the color of text box, because there is only one
instance of the text box. When you apply the color to the text box, what row
is it to act on?

Further, your example has 4 conditions..and conditional only allows 3
conditions.

You have to stick to conditional formatting.....
 
Caledonia said:
Need code help! On a form filled with data generated from a query, I need to
format the textboxes based on the data in one of the fields. Conditional
formatting works great, but apparently the pastels aren’t pastel enough so I
have to use custom colors. Here’s the code:

Private Sub Form_Current()
If Me.txtGroup = "A" Then
Me.txtID.BackColor = 12122104
ElseIf Me.txtGroup = "B" Then
Me.txtID.BackColor = 15979518
ElseIf Me.txtGroup = "C" Then
Me.txtID.BackColor = 16776652
ElseIf Me.txtGroup = "D" Then
Me.txtID.BackColor = 16628703

End If

End Sub

The view is “continuous forms” so it shows all the rows of data returned by
the query. The above code is making all of the rows change color depending
on where the cursor is. If the row has a “B”, then all the rows turn pink
(15979518). How do I make the rows show their respective color regardless of
where the cursor is?
Click to expand...


First, Conditional Formatting is the feature you want to use
for this, but you can only specify the default format plus 3
conditional formats. If you are thinking of more than those
four colors, then additional complications come into play.

Second, changing this stuff in the Current event is not what
you want.

Third, you can create your own custom colors by setting the
colors in the FormatCondition object. For example:

Private Sub Form_Open(Cancel As Integer)

Me.txtID.FormatConditions(0).BackColor = RGB(255,240,240)
Me.txtID.FormatConditions(1).BackColor = RGB(240,255,240)
Me.txtID.FormatConditions(2).BackColor = RGB(240,240,255)

End Sub
 
I assume the load or open event handling procedure would be a nice place.
Note that you can add more conditions, through VBA, than through the User
Interface. Andy Baron wrote some articles about that... but all I found, few
minutes ago, on Internet seems to require that you are registered member of
some site :-( (Google: Andy Baron Conditional Formatting Access).



Vanderghast, Access MVP
 
I think you can use more than 3 conditions through VBA, as discussed once by
Andy Baron (I don't find the reference at this moment).

Vanderghast, Access MVP
 
That's it!! I used yellow as the default and the other three worked
perfectly! Thank you so much!
 
I couldn't get it to work with more than 3 but I was able to use one of the
colors as a default. Worked great! Thanks!

Michel Walsh said:
I think you can use more than 3 conditions through VBA, as discussed once by
Andy Baron (I don't find the reference at this moment).

Vanderghast, Access MVP
Click to expand...
 
I know that the Expression Is option can use a function to
evaluate any number of conditions, but there can only be 3
sets of conditional properties. (The Add method of the
FormatConditions class generates an error on the fourth
condition.

The only way I know of getting more than 3 sets of
confitional properties is to stack multiple conditionally
formatted text boxes.

If you can find that article, I would be very interested in
seeing it. (I did find that there is an Access Advisor
article, but I can not view it.)
 
Still cannot find the original article, but, after all, it may have been
about Excel rather than with Access, or with Access but with reports, or a
hack in a specific case..., or something similar...

Vanderghast, Access MVP


Marshall Barton said:
I know that the Expression Is option can use a function to
evaluate any number of conditions, but there can only be 3
sets of conditional properties. (The Add method of the
FormatConditions class generates an error on the fourth
condition.

The only way I know of getting more than 3 sets of
confitional properties is to stack multiple conditionally
formatted text boxes.

If you can find that article, I would be very interested in
seeing it. (I did find that there is an Access Advisor
article, but I can not view it.)
--
Marsh
MVP [MS Access]


Michel said:
I think you can use more than 3 conditions through VBA, as discussed once
by
Andy Baron (I don't find the reference at this moment).

Vanderghast, Access MVP


"Marshall Barton" wrote
Click to expand...
Click to expand...
 
Back
Top