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. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|
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 |