I have a union query that I built so that I can include location average along with corporate average. I then sort the info on the graph so it is lowest to highes, so now the corporate average sits amongst the middle of the group. This givs a visual to where the locations compare to corporate. The problem is, I want the corporate one to be a different colo so it stands out. I know how to change it manually, but as the corporate average can move from quarter to quarter, I would like to use something like criteria where I can say if corp make datapoint dark blue. Is this even possible? If so how would I do this?
-
-
- First, set a Reference to the Microsoft Graph XX.X Object Library.
- Set the Color Property of an Interior Object, for a specific Member of the SeriesCollectio n Collection (Series Object):
Code:If YaDaYaDaYaDa Then 'By Series Name or Index Me![chtTest].SeriesCollection("2002").Interior.Color = vbBlue 'OR Me![chtTest].SeriesCollection(2).Interior.Color = vbBlue Else Me![chtTest].SeriesCollection("2002").Interior.Color = vbYellow 'OR Me![chtTest].SeriesCollection(2).Interior.Color = vbYellow End If
-
ok, so my graph is Graph2, my x axis is CountyOfSurvey and Y axis is Expr1. "Corp" is always one of the values in CountyOfSurvey, this is the one I want to be dark blue. I am assuming I put this in the VB editor under graph2? Can you help to make sure I get the code right?Comment
-
this isnt changing anything. What am I doing wrong?Code:Private Sub Graph2_Updated(Code As Integer) Me![Graph2].SeriesCollection("Corp").Interior.Color = vbYellow End SubComment
-
Corp is one of the data in the series, the series is CountyofSurveyComment
-
You would then have to check the value of the Data Point, then modify the Color of the Data Series, so the code to change the Color, depending on the Point Value would be:
P.S. - If you are still stuck, and you don't mind, I'll send you my E-Mail Address in a Private Message. You could then Send the Database to me as an Attachment, and as soon as I get a chance, I'll take a look at it. It is up to you.Code:Me![Graph2].SeriesCollection("CountyofSurvey").Interior.Color = vbYellowComment
-
This was a little more complicated then initially envisioned, but rather than going into a detailed, boring, discussion about this possible solution, I'll simply post the code. Should you have any further questions, feel free to ask. This code will replace the existing code in the Charts Command Button (Command17) on the OPCriteria Form.
P.S. - There is an occasional Error relating to Loading the ActiveX Control which I will leave to you to figure out.Code:'Seed the Random Number Generator Randomize 'At a bare minimum, there must be Quarter specified in the 1st Drop Down 'since this Value is used as a Criteria in multiple, relevant, Queries If IsNull(Me![Combo28]) Then MsgBox "You must enter at least 1 Quarter to analyze", vbExclamation, _ "No Quarter Defined" Me![Combo28].SetFocus Me![Combo28].Dropdown Exit Sub End If 'Open the Sub-Report as a Stand-Alone Report, apart from the context 'of the Main Form. This report is opened in Design/Hidden Mode DoCmd.OpenReport "OverallBySite", acViewDesign, , , acHidden 'Modify the Interior Color of the Point corresponding to Corp Value. This Interior 'Color will be assigned 1 of 16 possible Random Values (0 - 14) which will be 'passed to the QBColor() Function. Reports!OverallBySite.Report![Graph2].SeriesCollection(1).Points(9).Interior.Color = QBColor(Int(Rnd * 15) + 1) 'Close the Sub-Report containing the Chart, and Save the changes made DoCmd.Close acReport, "OverallBySite", acSaveYes 'Open the Main Report containing the Sub-Report DoCmd.OpenReport "OPGraphs", acPreviewComment
-
So, I got that done, and it always randoms the color, but here is the thing. I think I have you backwards. I need the color set to be random on point nine. "Corp" Does not fall on point 9 every time. It is the point I need to compare, if it is "Corp" no matter where it sits is what I need to vary. Sorry for the confusion.Comment
-
I guess you would have to iterate through all the Points in the Single Series, and see if its DataLabel is 'Corp'. If so then modify the Color. The pseudo code, NOT TESTED and intentionally kept lengthy for display purposes, would be analogous to:
Code:Dim intCounter As Integer For intCounter = 1 To Reports!OverallBySite.Report![Graph2].SeriesCollection(1).Points.Count If Reports!OverallBySite.Report![Graph2].SeriesCollection(1).Points(intCounter).DataLabel.Text = "Corp" Then Reports!OverallBySite.Report![Graph2].SeriesCollection(1).Points(intCounter).Interior.Color = vbYellow End If NextComment
-
You could also try to reference the Point by Name instead of Index, as in:
Code:Reports!OverallBySite.Report![Graph2].SeriesCollection(1).Points("Corp").Interior.Color = vbYellowComment
-
So would this go in palce of the code in the previous post? It gives me an error that OverallBySite is misspelled or refers to a report that isn't open or doent exist, If I have the report open, it errors Object doesnt support this property or method. If I have the report in design view, it opens but the color doesnt change. Any advice?Comment
Comment