determine color in graph in Access report

[Gab Franken]

Hi you all, thanx all of you in advance for your time helping me out.



I made an Access database for monitoring the projets running in my department. For your info, I am using Access 2003 (sorry, that's what the boss supplies).

For representing status of the progress of these projects to management, I made a report with two graphs: one piechart and one with columns, both representing same figures: status of projects.

That all went well, but unfortunately one of the staff-members is color-blind and therefore has some demands on the colors used. Normally I would adjust the colors in Visual Basic using the RGB color-coding, but when invoking the wizard for making of adjusting a graph the top-menu is replaced by the interface of MS Graph.

Therefore, can anybody help me out:

• How can I invole VBA when defining a graph
• And how can I then in VBA set the color of the pies in the piechart.

Kind regards,

Gab Franken

[June7]

Why can't you just manually preset the colors for the data series with the Graph properties?

Review http://www.developerbarn.com/communi...30/#post-28257

[Gab Franken]

Thank you for you quick reply!

Indeed setting the color with the Graph Properties would be the easiest way, but I need distinct colors, which are not included in the pre-programmed color scheme. I got the RGB-colorscheme for the exact colors our management can read, which I would like to put into VBA.

I tried your suggestion following your link. For the pie-chart it worked out well, although I could not understand the programming with the switch-command: what is "OL", or "GP-GM(S)" etc. I just made a few more lines, each line for each color : colors change, but a strange thing happened.
In the report underneath the graphs I put a table, each project is decribed in text and getting a colormark indicating its status (green-orange-red , in total 5 colors), and the same colorcoding I used in my pie-chart. The strange thing I mentioned is, that the color in the table differ quite a bit from the colors in the pie-chart. Even though I used exactly the same RGB-coding. The color of the table disappearing in the report is the right color; the color in the pie-chart is completely off-color for some reason.

Next to the piechart I have a graph with columns, the method mentioned wouldn't work at all: I get an error message. Solved that bit by leaving out the command 'point()' and increased the number in the command SeriesCollection by one each time. As a result, the same colors apear in the pie-chart and the bar-chart; but both differ from the colors in the table.

Any suggestions as on how to get the right colors in both the graphs??

[June7]

"OL" and "GP-GM(S)" are values in my data (soils classifications).

That is the extent of my experimentation with programmatically color coding graphs and I have abandoned it.

If you want to provide db for analysis, follow instructions at bottom of my post.

[Gab Franken]

"OL" and "GP-GM(S)" are values in my data (soils classifications).

That is the extent of my experimentation with programmatically color coding graphs and I have abandoned it.

If you want to provide db for analysis, follow instructions at bottom of my post.

Thank you for your kind offer, I attached de database. Removed all confidential data and removed tables, queries, forms and reports that are not important to this matter

[June7]

On the graphs, I think what you call oranje I would call yellow and rood looks like red brick to me.

I see two different RGB codes for color groen. From the color picker 71 255 100 is the same as shown in the textbox. I don't know why the graph shows the dark green. Must be the MSGraph engine doesn't handle the RGB codes and substitutes whatever color seems close.

Regardless, I get the same color discrepancy for groen and oranje (blauw, rood, white look okay).

The constant vbGreen is the same in graph and textbox. Maybe management will have to adapt to new colors.

I am surprised the code for coloring the textbox actually works. Think I've tried this before and didn't work, interesting.

The report renders very fast if the graph colors are manually set in graph properties instead of with code. Conditional Formatting could color the textbox.

[Gab Franken]

On the graphs, I think what you call oranje I would call yellow and rood looks like red brick to me.

I see two different RGB codes for color groen. From the color picker 71 255 100 is the same as shown in the textbox. I don't know why the graph shows the dark green. Must be the MSGraph engine doesn't handle the RGB codes and substitutes whatever color seems close.

Regardless, I get the same color discrepancy for groen and oranje (blauw, rood, white look okay).

The constant vbGreen is the same in graph and textbox. Maybe management will have to adapt to new colors.

I am surprised the code for coloring the textbox actually works. Think I've tried this before and didn't work, interesting.

The report renders very fast if the graph colors are manually set in graph properties instead of with code. Conditional Formatting could color the textbox.

Hi June7,

Thank you for your quick reply. You are right that the colors of graph and table differ: I used different colorcoding espacially for green. The reason is, as you also state, that MS Graph engine doens't handle the RGB codes well. And using the same color-coding gives me an awfull green in the graphs. Pitty you also don't have a clue on how to overcome this problem.

For your info; I have to use colorcoding in code in stead of using the method of graph properties, as my customer is color blind, and I have to use colors that he is able to distuinguish.

And yes, colorcoding works, thanx again for your good advise!

Greetz, Gab Franken

[June7]

You got the color coding to give acceptable colors? I think you should be able to set acceptable colors manually in the chart properties and textbox Conditional Formatting.

[Gab Franken]

You got the color coding to give acceptable colors? I think you should be able to set acceptable colors manually in the chart properties and textbox Conditional Formatting.

Hi Jun7,

mmm, that indeed is the very best idea, but being just an amateur, I really don't have a clue on how to set these colors manually in the chart properties and textbox. Would you please be so kind as to help me out?

Greetz, Gab

[June7]

Do you know what textbox Conditional Formatting is?

Manually setting properties of graph can be tricky, involves lots of clicking. With report in design view, double click on graph (or right click > Chart Object > Open). Click on one data series (pie segment or bar) to select then double click to open property sheet, set color, texture, pattern, fill effects.

[Gab Franken]

Do you know what textbox Conditional Formatting is?

Manually setting properties of graph can be tricky, involves lots of clicking. With report in design view, double click on graph (or right click > Chart Object > Open). Click on one data series (pie segment or bar) to select then double click to open property sheet, set color, texture, pattern, fill effects.

Thank you for your ever quick reactions!

Yes, this bit I know, I thought you meant, that it is possible to define the colors that appear in the overview whenn adjusting the color of the graph or when adjusting the color in the conditional formatting of a textbox. For reasons mentioned earlier I just can't use the colors that standardly appear in Access. It does not sound strange to me that it would be possible to define somewhere in access what colors appear in the overview. If you happen to know how this can be done i would so gratefull.

[June7]

Remind me again, what is 'overview' - the graphs?

If the graphs aren't going to accept those codes then you are probably stuck with the color palette. Unfortunately, textbox Conditional Formatting doesn't have pattern, texture, gradient.

However, I am confused. You said in post 3 that the colors in textboxes are correct and the pie chart is wrong. The colors in textbox are from the color palette.

[Gab Franken]

Remind me again, what is 'overview' - the graphs?

If the graphs aren't going to accept those codes then you are probably stuck with the color palette. Unfortunately, textbox Conditional Formatting doesn't have pattern, texture, gradient.

However, I am confused. You said in post 3 that the colors in textboxes are correct and the pie chart is wrong. The colors in textbox are from the color palette.

Forgive me for my English, it is not my first language. Some confusing can be led back to that circumstance. In Overview I meant as you indeed suggested, the colorpallet.

In Post 3 I indeed mentioned, the colors in the textbox are right, but also there I didn't use conditional formatting, but coding in VBA with -if- and -elseif- commands:
If [VoortgangKleur] = 1 Then
Me.[VoortgangKleur].BackColor = RGB(71, 255, 181) 'groen
Me.[VoortgangKleur].ForeColor = RGB(71, 255, 181) 'groen
ElseIf [VoortgangKleur] = 3 Then
Me.[VoortgangKleur].BackColor = RGB(232, 230, 72) 'oranje
Me.[VoortgangKleur].ForeColor = RGB(232, 230, 72) 'oranje
ElseIf [VoortgangKleur] = 5 Then
Me.[VoortgangKleur].BackColor = RGB(255, 102, 0) 'rood
Me.[VoortgangKleur].ForeColor = RGB(255, 102, 0) 'rood
ElseIf [VoortgangKleur] = 100 Then
Me.[VoortgangKleur].BackColor = vbWhite
Me.[VoortgangKleur].ForeColor = vbWhite
Else: Me.[VoortgangKleur].BackColor = RGB(72, 147, 255) 'blauw
Me.[VoortgangKleur].ForeColor = RGB(72, 147, 255) 'blauw
End If

Using the same colorcoding for my graphs in the method described by you, strangly MS Graph doesn't show the same colors, which strikes me as odd. So possibly you can help me out again with some ideas as on how to change the colors in the colorpallet. Possibly that will lead to optimum colors in both textboxes and graph.

[June7]

I already described in post 10 how to manually set colors in textbox and graph by selecting from the color palette.

[Gab Franken]

I already described in post 10 how to manually set colors in textbox and graph by selecting from the color palette.

Yeah, I know, but I want te determine what colors appear in the colorpallet, so to say, change the default colors in this pallet by user-defined colors based on RGB coding. Is that possible?