A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

HELP: Conditional Formatting with more then 3 conditions



 
 
Thread Tools Display Modes
  #21  
Old June 27th, 2007, 07:26 PM posted to microsoft.public.access.reports
cynteeuh
external usenet poster
 
Posts: 31
Default Conditional Formatting with more then 3 conditions

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!

"Marshall Barton" wrote:

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]


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.

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

"Marshall Barton" wrote:

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


"Marshall Barton" wrote:

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.



  #22  
Old June 27th, 2007, 08:22 PM posted to microsoft.public.access.reports
Marshall Barton
external usenet poster
 
Posts: 5,361
Default Conditional Formatting with more then 3 conditions

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]


cynteeuh wrote:
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?


"Marshall Barton" wrote:
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.


  #23  
Old June 28th, 2007, 06:00 PM posted to microsoft.public.access.reports
cynteeuh
external usenet poster
 
Posts: 31
Default Conditional Formatting with more then 3 conditions

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


"Marshall Barton" wrote:

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]


cynteeuh wrote:
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?


"Marshall Barton" wrote:
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.



  #24  
Old June 28th, 2007, 06:28 PM posted to microsoft.public.access.reports
cynteeuh
external usenet poster
 
Posts: 31
Default Conditional Formatting with more then 3 conditions

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-)
--
"Marshall Barton" wrote:

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]


cynteeuh wrote:
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?


"Marshall Barton" wrote:
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.



  #25  
Old June 28th, 2007, 08:25 PM posted to microsoft.public.access.reports
Marshall Barton
external usenet poster
 
Posts: 5,361
Default Conditional Formatting with more then 3 conditions

cynteeuh wrote:

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.

--
Marsh
MVP [MS Access]
  #26  
Old June 28th, 2007, 10:20 PM posted to microsoft.public.access.reports
cynteeuh
external usenet poster
 
Posts: 31
Default Conditional Formatting with more then 3 conditions

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 . . .

"Marshall Barton" wrote:

cynteeuh wrote:

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.

--
Marsh
MVP [MS Access]

  #27  
Old June 28th, 2007, 11:36 PM posted to microsoft.public.access.reports
Marshall Barton
external usenet poster
 
Posts: 5,361
Default Conditional Formatting with more then 3 conditions

cynteeuh wrote:

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.

--
Marsh
MVP [MS Access]
  #28  
Old June 29th, 2007, 12:44 AM posted to microsoft.public.access.reports
cynteeuh
external usenet poster
 
Posts: 31
Default Conditional Formatting with more then 3 conditions

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
--
Cyn

"Marshall Barton" wrote:

cynteeuh wrote:

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.

--
Marsh
MVP [MS Access]

  #29  
Old June 29th, 2007, 02:09 AM posted to microsoft.public.access.reports
Marshall Barton
external usenet poster
 
Posts: 5,361
Default Conditional Formatting with more then 3 conditions

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
--
Marsh
MVP [MS Access]


cynteeuh wrote:

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


  #30  
Old June 29th, 2007, 05:38 AM posted to microsoft.public.access.reports
krissco
external usenet poster
 
Posts: 167
Default Conditional Formatting with more then 3 conditions

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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
conditional formatting in form slows down calculations Susan Using Forms 5 June 13th, 2005 11:24 AM
Conditional Formatting Error ddate Worksheet Functions 0 May 5th, 2005 09:00 PM
Conditional Formatting Anomaly Rutgers_Excels General Discussion 1 November 1st, 2004 09:43 PM
Freezing Conditional Formatting Ashish Chamaria Worksheet Functions 3 December 19th, 2003 11:54 AM
Conditional formatting with formula Ann Scharpf Worksheet Functions 5 October 21st, 2003 04:29 AM


All times are GMT +1. The time now is 02:12 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.