VBA to Conditionally Format a Chart (1 Viewer)

[June7]

Using data driven approach, built charts in Modern and Classic without VBA. Not liking text quality in Modern. Calibri used in both. This is in Access 2021.

 

[ChrisMore]

Glad you're happy with it!
As I said (after editing my initial reply), there was no need to requery the chart itself as that happens automatically
You can probably remove the Refresh button now as well.


When I tested it failed on the Cutomer_Order_Form ... Goods_Out_Search_Button_Click on the line
If DLookup("[Tag]", "Customer_Orders", "[Order_Number] ='" & Me.Text67 & "'") = True Then

However that button doesn't actually exist so that code procedure could be deleted.
When I did so it compiled.
However if you add Option Explicit to each code module there may well be other compilation issues - not tested.

I think that issue is because I scaled down the sample database I uploaded. My version is working fine.

I am noticing there is an issue with the reports below the chart when using the RequeryObjects sub. I can this is also apparent on your version you uploaded. When comparing the report records from my original upload there are a lot more for each working day, whereas for the updated version you uploaded the records have greatly reduced. Any ideas why?

 

[ChrisMore]

Using data driven approach, built charts in Modern and Classic without VBA. Not liking text quality in Modern. Calibri used in both. This is in Access 2021.

Thanks for the suggestion, please can you upload your edited version of the database so I can have a look?

I am wondering if your results are similar to what I have done. The reason I say this is because the blue bar is still there. Going back to my original post, I am looking to have the blue bar automatically recolored based on the value. So in your example screen shots the blue bar should be green (preferably a different shade green). Then when the value is over 50 the bar will be yellow and so on.

 

[isladogs]

I am noticing there is an issue with the reports below the chart when using the RequeryObjects sub. I can this is also apparent on your version you uploaded. When comparing the report records from my original upload there are a lot more for each working day, whereas for the updated version you uploaded the records have greatly reduced. Any ideas why?

Yes I can see what you mean.
Either there is an error in the original code you used or (much) more likely I have made a mistake somewhere.

I didn't think there is an issue with the RequeryObjects code I used, but that now looks like the problem.
As far as I can recall I didn't alter any of your queries.

I thought it might be the simplified code I used for getting the values in each text box but I didn't use them in the version I uploaded.
Attached is a version where I have simplified the textbox 'calculations' for each button click.
All look OK to me . . . but that didn't solve the issue about the reduction in data

As a final test, I modified the RequeryObjects replacing .Requery with .Report.Requery on each line.
The 'missing' data was restored. For example:

Please carefully check whether the additional data is correct this time around.

 

[ChrisMore]

Please carefully check whether the additional data is correct this time around.

Thank you, that did the trick. I've checked the data and it appears to all be there.

Whilst were on the subject of modern charts, I have a question. Is it possible for the Y axis values to go up in multiples of 20. I believe this is called the major unit in classic charts. So I'd like 0%, 20%, 40%, 60%, 80%, 100% displayed in the Y axis.

 

[isladogs]

Thank you, that did the trick. I've checked the data and it appears to all be there.

Whilst were on the subject of modern charts, I have a question. Is it possible for the Y axis values to go up in multiples of 20. I believe this is called the major unit in classic charts. So I'd like 0%, 20%, 40%, 60%, 80%, 100% displayed in the Y axis.

Its such a basic request but that functionality is not currently available in modern charts
There are various axis related properties you can change either in code or from the property sheet:

However selecting major / minor units is not available. A significant omission.

 

[ChrisMore]

That's a shame, it's only an aesthetic choice for me because I'm not keen on the values being staggered as they currently are, and I'm not able to increase the width of the chart due to space constraints on the report. Hopefully the team can add that function in! I could go back to a classic chart but I'm locked in on the improved visuals of modern charts.

 

[isladogs]

I’ve raised it before as an issue. Will do so again but, even if they agree, there are other major new features being worked on that need to be completed first

 

[ChrisMore]

Yeah, that's understandable. Please do let me know if they come back to you about conditional formatting.

For all of the excellent progress I've made with your help, technically my original post of seeking help with automatic colour formatting a chart still isn't resolved. But I sincerely thank you for all of your time and effort to help me. I have learnt a lot along the way

 

[isladogs]

Yes I’m aware that the original request re CF wasn’t resolved. I believe it’s not possible at the moment but will certainly let you know if I hear otherwise

I had also asked CoPilot before @June7’s post. It also stated it wasn’t directly possible and gave several alternatives including the same answer about creating several series: one for each colour range.
Other ideas it suggested were to analyze the JSON behind the chart and it also gave what it called a Colin only tip based on ‘our’ previous exchanges and which was to reverse engineer the chart!