Series color in access VBA depending on series name

Question

I am working on an access database in which I used a modern chart and I want to set it's series color depending on series name by VBA codes. how can I do that? This codes I used for that purpose Thanks for your help...

With MyChart

i = .ChartSeriesCollection.Count
For Each series In .ChartSeriesCollection
  Select Case series.name
    Case DLookup("[MsgPrompot]", "tblPrompots", "[MsgID] = 40")
      lngColorVar = Nz(DLookup("lngColor", "tblPrompots", "[MsgID] = 40"), 0)
      .ChartSeriesCollection.item(i).BorderColor = lngColorVar
      .ChartSeriesCollection.item(i).FillColor = lngColorVar

  Case DLookup("[MsgPrompot]", "tblPrompots", "[MsgID] = 41")
 lngColorVar = Nz(DLookup("lngColor", "tblPrompots", "[MsgID] = 41"), 0)
      .ChartSeriesCollection.item(i).BorderColor = lngColorVar
      .ChartSeriesCollection.item(i).FillColor = lngColorVar

  Case DLookup("[MsgPrompot]", "tblPrompots", "[MsgID] = 42")
 lngColorVar = Nz(DLookup("lngColor", "tblPrompots", "[MsgID] = 42"), 0)
      .ChartSeriesCollection.item(i).BorderColor = lngColorVar
      .ChartSeriesCollection.item(i).FillColor = lngColorVar
 
 End Select
 i = i - 1
 Next 
End With

"how can I do that?" - generate a 32-bit unsigned integer [hash (`UInt32`) of the series-name `String` value](https://stackoverflow.com/questions/14717526/vba-hash-string), then convert that to a 24bpp RGB color. — Dai, Oct 08 '22 at 09:11
What's wrong with your code - error message, wrong result, nothing happens? — June7, Oct 08 '22 at 17:40
What is in field "MsgPrompot" - if it is not series names, then this code makes no sense. Edit question to show sample data as text tables. — June7, Oct 08 '22 at 19:32
And why would you need a DLookup that has static criteria? Why not just static text for the Case match? Does data in MsgPrompot field change for the 3 MsgID values - how? There are always just 3 series? — June7, Oct 08 '22 at 20:23
Hi dear friend, thanks for you to pay attention to my problem, I wrote my series names in 'central kurdish' fonts which VBA doesn't support and it shows them in symbols so that I wrote them in a table named 'tblPrompots' and a column named 'LayeniSiyasi', the column has 7 rows, I want my chart shows it's series color depending on series names and I have a list box to help user choose several rows from it be shown in the chart if the user doesn't want to show all of them — Gourge Gourginian, Oct 09 '22 at 06:36

score 0 · Answer 1 · answered Oct 10 '22 at 16:04

I handled this problem in a recent database by adding a long field for color to the lookup table of departments. In your case, I would add it to the tblPrompts.

You can switch an RGB number to a long number easily. In the Debug window, type ?RGB(255, 252, 127) and it will return the long number 8387839.

In your DLookup statement, bring back the field with the lngColor and place in a variable. This would give you a variable to set the color of your fill and border color.

So, assume you create a field named lngColor in your tblPrompts and you create a variable named lngColorVar

This would change your code to:

Changed Code

dim lngColorVar as long
With MyChart

i = .ChartSeriesCollection.Count
For Each series In .ChartSeriesCollection
  Select Case series.name
    Case DLookup("[MsgPrompot]", "tblPrompots", "[MsgID] = 40")
      lngColorVar = Nz(dlookup("lngColor", "tblPrompots", "[MsgID] = 40"),0)
      .ChartSeriesCollection.item(i).BorderColor = lngColorVar
      .ChartSeriesCollection.item(i).FillColor = lngColorVar

Hope this helps, Susan

Hi dear friend, As you can see above, I changed the codes، but it didn't help. What do you mind if I send you an example from my database? — Gourge Gourginian, Oct 11 '22 at 06:55

Series color in access VBA depending on series name

1 Answers1