HELP: Conditional Formatting with more then 3 conditions

[cynteeuh]

I copied the below and pasted per your instructions, but nothing changed. I'm
thinking it may be my unbound text box. There isn't any control source. This
is the box I'd like shaded per the colors based on the name. It is next to
the field/textbox that has the name. Any suggestions? :-/

Also, was I supposed to leave the name " 'lt blue" in the code?
Is the last color purple? If not, what is the code for purple?

Thanks AGAIN for your help!!! I truly appreciate it!

First, open the report )the one you are using as a
subreport) in Design View. Then click on a *blank* area of
the report section (Detail?) that contains the name text
box. In the properties list, under the Events tab, select
[Event Procedure] from the drop list on the right side of
the OnFormat property. That should open the report's VBA
module and put the cursor in the event procedure, ready for
you to enter the code:

Select Case Me.coname
Case "John"
Me.coname.BackColor = vbRed
Case "Cyn"
Me.coname.BackColor = Rgb(220,220,255) 'lt blue
Case "Rod"
Me.coname.BackColor = vbBlue
Case "Antonio"
Me.coname.BackColor = vbGreen
Case Else
Me.coname.BackColor = vbWhite
End Select
--
Marsh
MVP [MS Access]

Sorry to be a pain, but it is a bit over my head. Where would I enter this
info? Is it in the properties, which has five tabs (format, data, event,
other, & all)? If so, where or how would you enter this?

I'm trying to create a grid with the color chart next to the name.

Color CoName
Red John
Lt Blue Cyn
Dk Blue Rod
Green Antonio

Therefore, if the field coname is "john", the box is shaded red. If it's
"cyn", the box is shaded lt. blue, etc. This is a report based on a crosstab
query that I'd like to embed as a sub rpt in the rpt header. I'm using it as
a legend. It's working, except for the conditional formatting limitation. The
current rpt is block style (spreadsheet format). The first column is an
unbound text box (shaded by conditional formatting based on [coname]). The
next column is the coname. The remainder columns are the totals.

Thanks AGAIN for helping on this! Apologies for my ignorance!

The general idea for doing this in the report Detail
section's Format event procedure is something like:

Select Case Me.coname
Case "x"
Me.coname.BackColor = vbRed
Case "y"
Me.coname.BackColor = Rgb(220,220,255) 'lt blue
Case "z"
Me.coname.BackColor = vbBlue
. . .
Case Else
Me.coname.BackColor = vbWhite
End Select

If that's too confusing for you, then explain more about the
"x" and anything else you don't follow.
--
Marsh
MVP [MS Access]


cynteeuh wrote:
Basically, if [coname]=x, then use color per the below. I've setup the
conditional formatting for three, but I can't do the additional due to
limitation.

Red
Lt Blue
Dk Blue
Purple
Green


:

cynteeuh wrote:

I don't know VBA. Is there any other alternative for more than 3 conditions
in conditonal formatting? I need six or five since I can use the default as
the sixth.


Well there is, but it's more complicated than the simple
code that you can use in a report (or single view form, but
not in a continuous or datasheet form).

If you will explain the conditions you want to use
(including the names of the text box controls), I'll take a
shot at the code to do it.

 

[Marshall Barton]

Did you make sure that the section's OnFormat property
contains [Event Procedure]

Another reason for nothing happening is that the coname text
box doesn't really have the values "John", "Cyn", etc. so
the Case Else was in effect. This can be verified by
changing vbWhite to vbBlack so you can see when it happens.

If the colored text box is not the coname text box, then
change the coname to the name of the colored text box in all
of the lines like:
Me.[color text box name].BackColor = vbRed

You did not have a person assigned to purple. The code
depends on what you think purple looks like. I kind of like
this one: RGB(190,85,255)

There is no predefined VBA variable for Lt Blue so I guessed
at a shade that might be acceptable. Look up the RGB
function in VBA Help for details about how to specify
colors.

If you have additional qusestions on this topic, please post
copy/paste the code as you have it along with your question.
--
Marsh
MVP [MS Access]

I copied the below and pasted per your instructions, but nothing changed. I'm
thinking it may be my unbound text box. There isn't any control source. This
is the box I'd like shaded per the colors based on the name. It is next to
the field/textbox that has the name. Any suggestions? :-/

Also, was I supposed to leave the name " 'lt blue" in the code?
Is the last color purple? If not, what is the code for purple?

First, open the report )the one you are using as a
subreport) in Design View. Then click on a *blank* area of
the report section (Detail?) that contains the name text
box. In the properties list, under the Events tab, select
[Event Procedure] from the drop list on the right side of
the OnFormat property. That should open the report's VBA
module and put the cursor in the event procedure, ready for
you to enter the code:

Select Case Me.coname
Case "John"
Me.coname.BackColor = vbRed
Case "Cyn"
Me.coname.BackColor = Rgb(220,220,255) 'lt blue
Case "Rod"
Me.coname.BackColor = vbBlue
Case "Antonio"
Me.coname.BackColor = vbGreen
Case Else
Me.coname.BackColor = vbWhite
End Select

Sorry to be a pain, but it is a bit over my head. Where would I enter this
info? Is it in the properties, which has five tabs (format, data, event,
other, & all)? If so, where or how would you enter this?

I'm trying to create a grid with the color chart next to the name.

Color CoName
Red John
Lt Blue Cyn
Dk Blue Rod
Green Antonio

Therefore, if the field coname is "john", the box is shaded red. If it's
"cyn", the box is shaded lt. blue, etc. This is a report based on a crosstab
query that I'd like to embed as a sub rpt in the rpt header. I'm using it as
a legend. It's working, except for the conditional formatting limitation. The
current rpt is block style (spreadsheet format). The first column is an
unbound text box (shaded by conditional formatting based on [coname]). The
next column is the coname. The remainder columns are the totals.

 

[cynteeuh]

Ok, the below is what I have in the "Event Procedure" window. When I changed
white to black, it still didn't do anything. The same is true by changing
text box to actual name of field. No results. Instead the text box has the
actual field names and no color.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Select Case Me.AcqdByCoName
Case "Amazon"
Me.AcqdByCoName.BackColor = vbRed
Case "Intel"
Me.AcqdByCoName.BackColor = RGB(220, 220, 255)
Case "RealNetworks"
Me.AcqdByCoName.BackColor = vbBlue
Case "Intuit"
Me.AcqdByCoName.BackColor = vbGreen
Case Else
Me.AcqdByCoName.BackColor = vbBlack
End Select

End Sub

Did you make sure that the section's OnFormat property
contains [Event Procedure]

Another reason for nothing happening is that the coname text
box doesn't really have the values "John", "Cyn", etc. so
the Case Else was in effect. This can be verified by
changing vbWhite to vbBlack so you can see when it happens.

If the colored text box is not the coname text box, then
change the coname to the name of the colored text box in all
of the lines like:
Me.[color text box name].BackColor = vbRed

You did not have a person assigned to purple. The code
depends on what you think purple looks like. I kind of like
this one: RGB(190,85,255)

There is no predefined VBA variable for Lt Blue so I guessed
at a shade that might be acceptable. Look up the RGB
function in VBA Help for details about how to specify
colors.

If you have additional qusestions on this topic, please post
copy/paste the code as you have it along with your question.
--
Marsh
MVP [MS Access]

I copied the below and pasted per your instructions, but nothing changed. I'm
thinking it may be my unbound text box. There isn't any control source. This
is the box I'd like shaded per the colors based on the name. It is next to
the field/textbox that has the name. Any suggestions? :-/

Also, was I supposed to leave the name " 'lt blue" in the code?
Is the last color purple? If not, what is the code for purple?

First, open the report )the one you are using as a
subreport) in Design View. Then click on a *blank* area of
the report section (Detail?) that contains the name text
box. In the properties list, under the Events tab, select
[Event Procedure] from the drop list on the right side of
the OnFormat property. That should open the report's VBA
module and put the cursor in the event procedure, ready for
you to enter the code:

Select Case Me.coname
Case "John"
Me.coname.BackColor = vbRed
Case "Cyn"
Me.coname.BackColor = Rgb(220,220,255) 'lt blue
Case "Rod"
Me.coname.BackColor = vbBlue
Case "Antonio"
Me.coname.BackColor = vbGreen
Case Else
Me.coname.BackColor = vbWhite
End Select


cynteeuh wrote:
Sorry to be a pain, but it is a bit over my head. Where would I enter this
info? Is it in the properties, which has five tabs (format, data, event,
other, & all)? If so, where or how would you enter this?

I'm trying to create a grid with the color chart next to the name.

Color CoName
Red John
Lt Blue Cyn
Dk Blue Rod
Green Antonio

Therefore, if the field coname is "john", the box is shaded red. If it's
"cyn", the box is shaded lt. blue, etc. This is a report based on a crosstab
query that I'd like to embed as a sub rpt in the rpt header. I'm using it as
a legend. It's working, except for the conditional formatting limitation. The
current rpt is block style (spreadsheet format). The first column is an
unbound text box (shaded by conditional formatting based on [coname]). The
next column is the coname. The remainder columns are the totals.

 

[cynteeuh]

NEVER MIND -- I got it to work! I had used the wrong name for the text box! I
was using the control source name and not the text box name, which is
"text33". #-o

I also changed the text box from unbound to the control source with the
values.

It works beautifully now, except the names/values are in the box. I only
wanted a shaded box with no text. Any suggestions?

THANKS SO MUCH for hanging in there with me!!!!!! I feel like an idiot! L-)
--

Did you make sure that the section's OnFormat property
contains [Event Procedure]

Another reason for nothing happening is that the coname text
box doesn't really have the values "John", "Cyn", etc. so
the Case Else was in effect. This can be verified by
changing vbWhite to vbBlack so you can see when it happens.

If the colored text box is not the coname text box, then
change the coname to the name of the colored text box in all
of the lines like:
Me.[color text box name].BackColor = vbRed

You did not have a person assigned to purple. The code
depends on what you think purple looks like. I kind of like
this one: RGB(190,85,255)

There is no predefined VBA variable for Lt Blue so I guessed
at a shade that might be acceptable. Look up the RGB
function in VBA Help for details about how to specify
colors.

If you have additional qusestions on this topic, please post
copy/paste the code as you have it along with your question.
--
Marsh
MVP [MS Access]

I copied the below and pasted per your instructions, but nothing changed. I'm
thinking it may be my unbound text box. There isn't any control source. This
is the box I'd like shaded per the colors based on the name. It is next to
the field/textbox that has the name. Any suggestions? :-/

Also, was I supposed to leave the name " 'lt blue" in the code?
Is the last color purple? If not, what is the code for purple?

First, open the report )the one you are using as a
subreport) in Design View. Then click on a *blank* area of
the report section (Detail?) that contains the name text
box. In the properties list, under the Events tab, select
[Event Procedure] from the drop list on the right side of
the OnFormat property. That should open the report's VBA
module and put the cursor in the event procedure, ready for
you to enter the code:

Select Case Me.coname
Case "John"
Me.coname.BackColor = vbRed
Case "Cyn"
Me.coname.BackColor = Rgb(220,220,255) 'lt blue
Case "Rod"
Me.coname.BackColor = vbBlue
Case "Antonio"
Me.coname.BackColor = vbGreen
Case Else
Me.coname.BackColor = vbWhite
End Select


cynteeuh wrote:
Sorry to be a pain, but it is a bit over my head. Where would I enter this
info? Is it in the properties, which has five tabs (format, data, event,
other, & all)? If so, where or how would you enter this?

I'm trying to create a grid with the color chart next to the name.

Color CoName
Red John
Lt Blue Cyn
Dk Blue Rod
Green Antonio

Therefore, if the field coname is "john", the box is shaded red. If it's
"cyn", the box is shaded lt. blue, etc. This is a report based on a crosstab
query that I'd like to embed as a sub rpt in the rpt header. I'm using it as
a legend. It's working, except for the conditional formatting limitation. The
current rpt is block style (spreadsheet format). The first column is an
unbound text box (shaded by conditional formatting based on [coname]). The
next column is the coname. The remainder columns are the totals.

 

[Marshall Barton]

NEVER MIND -- I got it to work! I had used the wrong name for the text box! I
was using the control source name and not the text box name, which is
"text33". #-o

I also changed the text box from unbound to the control source with the
values.

It works beautifully now, except the names/values are in the box. I only
wanted a shaded box with no text. Any suggestions?

THANKS SO MUCH for hanging in there with me!!!!!! I feel like an idiot! L-)

You are not allowed to feel like an idiot until you make the
same mistake three times ;-) Live and learn!

To color the other text box, change the code so it looks
more like:

Me.[name of color text box].BackColor = . . .

and make sure the color text box is unbound.

 

[cynteeuh]

Thanks for the encouraging words! I feel a lil' better.

Sorry to drag this on, but (sadly) I still can't get my unbound text box to
be shaded per the value of another field (the same five variables in coding
below -- Amazon=Red, Intel=Blue, etc.).

It works with the coding in earlier post, but the values name's are in the
box (I've got a text box with the name shaded by the color based on the
value). I only want the text box to be shaded and no text. Is that possible?
I couldn't find a way to make the text font transparent.

You're solid, THANK you AGAIN so VERY MUCH!
--
Cyn
Desparately trying to grasp this . . .

NEVER MIND -- I got it to work! I had used the wrong name for the text box! I
was using the control source name and not the text box name, which is
"text33". #-o

I also changed the text box from unbound to the control source with the
values.

It works beautifully now, except the names/values are in the box. I only
wanted a shaded box with no text. Any suggestions?

THANKS SO MUCH for hanging in there with me!!!!!! I feel like an idiot! L-)

You are not allowed to feel like an idiot until you make the
same mistake three times ;-) Live and learn!

To color the other text box, change the code so it looks
more like:

Me.[name of color text box].BackColor = . . .

and make sure the color text box is unbound.

 

[Marshall Barton]

Thanks for the encouraging words! I feel a lil' better.

Sorry to drag this on, but (sadly) I still can't get my unbound text box to
be shaded per the value of another field (the same five variables in coding
below -- Amazon=Red, Intel=Blue, etc.).

It works with the coding in earlier post, but the values name's are in the
box (I've got a text box with the name shaded by the color based on the
value). I only want the text box to be shaded and no text. Is that possible?
I couldn't find a way to make the text font transparent.

You're solid, THANK you AGAIN so VERY MUCH!

Hmmm, I guess I better see a copy of the code that doesn't
work.

Maybe I can take a shot at some of the things that might
cause nothing to happen. Since the color works when you set
the color of the AcqdByCoName text box but doesn't work when
you try to set it on some other text box, you better make
sure the other (better if I knew the real name) text box's
BackStyle property is set to Normal. Are you sure it really
is a text box (or label) and not some other kind of control?

This exercise is very possible. Actually it's a fairly
simple to do . . . once you understand what it's all about.

 

[cynteeuh]

Yes, it's a text box and the BackStyle is normal.

I don't think it's in the coding, because when I change the working text
box's control source to blank "unbound", the text box defaults to the "Case
Else", which is purple. I end up with five solid colored purple boxes without
any data in it. I'd like the five boxes colored per vba code, but without the
names/data in it.

In other words, I'm trying to get a text box without the company name's in
the box. The working rpt shows text box with the company name in a white font
and shaded per the vba code. I'd prefer the colored/shaded boxes without the
company name in it. Therefore I'd have a shaded box (per the code), then a
text box next to that with company name. I hope that makes sense. :-<

e.g.
Red Shaded Box Amazon
Green Shaded Box Intuit

I know this is simple and I'm making it hard. Pls forgive me! ;-l

 

[Marshall Barton]

You forgot to post a copy of the code that is not working.
I also want to know the name of the text box you want to
have the colors.

I just thought of one other mistake you might have made.
You didn't change the text box name in the Select Case
statement did you? It should still be the bound text box:
Select Case Me.AcqdByCoName

 

[krissco]

If a hack is acceptable, you can change the forecolor as well (text
the same color as the background) and that will effectively "hide" the
text within the control. The text will still be viewable when selected
with the mouse.

-Kris

 

[angie]

i have entered the following code in the OnFormatEvent of the Detail section:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Select Case Me.category
Case "A"
Me.client.BackColor = 12632256
Case "B"
Me.client.BackColor = 12632256
Case "C"
Me.client.BackColor = 12632256
Case Else
Me.client.BackColor = 16777215
End Select

Select Case Me.category
Case "dat"
Me.client.FontUnderline = yes
Case Else
Me.client.FontUnderline = no
End Select

End Sub

but i get the formatting only with the back color, not with the font
underline. have i done something wrong with the code?

Ο χÏήστης "Wayne Morgan" έγγÏαψε: