Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2009
    Posts
    204

    Unanswered: Color series collection of a report chart using vba

    Hello,

    I have a chart (graph38) in a report (rpt_Attrition) that shows employee tenure. I need to color the series based on the series value (the value is the number of employees, so I do a dlookup to color based on the number of years). This does not work. I'm hoping one of you could point me in the right direction?

    This is what I'm attempting:

    Code:
    Dim int1 As Integer, i As Integer
    int1 = DCount("[YearNum]", "qry_EmployeeTenure")
    i = 0
    Do Until i = int1
        If DLookup("YearNum", "qry_EmployeeTenure", "[CountOfEmployeeName] = """ & Me.Graph38.SeriesCollection(i) & """") = "< 1Y" Then
            Me.Graph38.SeriesCollection(i).Interior.Color = RGB(220, 230, 242)
        Else
            If DLookup("YearNum", "qry_EmployeeTenure", "[CountOfEmployeeName] = """ & Me.Graph38.SeriesCollection(i) & """") = "1 Y" Then
                Me.Graph38.SeriesCollection(i).Interior.Color = RGB(185, 205, 229)
            Else
                If DLookup("YearNum", "qry_EmployeeTenure", "[CountOfEmployeeName] = """ & Me.Graph38.SeriesCollection(i) & """") = "2 Y" Then
                    Me.Graph38.SeriesCollection(i).Interior.Color = RGB(149, 179, 215)
                Else
                    If DLookup("YearNum", "qry_EmployeeTenure", "[CountOfEmployeeName] = """ & Me.Graph38.SeriesCollection(i) & """") = "3 Y" Then
                        Me.Graph38.SeriesCollection(i).Interior.Color = RGB(79, 129, 189)
                    Else
                        If DLookup("YearNum", "qry_EmployeeTenure", "[CountOfEmployeeName] = """ & Me.Graph38.SeriesCollection(i) & """") = "4 Y" Then
                            Me.Graph38.SeriesCollection(i).Interior.Color = RGB(55, 96, 146)
                        Else
                            Me.Graph38.SeriesCollection(i).Interior.Color = RGB(37, 64, 97)
                        End If
                    End If
                End If
            End If
        End If
    i = i + 1
    Loop
    This is what works for coloring one line:
    Code:
    With Me.Graph38
    .SeriesCollection(1).Interior.Color = RGB(220, 230, 242)
    End with

  2. #2
    Join Date
    Oct 2009
    Posts
    204
    This is what the query looks like where I am looking up

    NumYears| CountOfEmployeeName| Years| YearNum
    3| 13| Years| 3 Y
    2| 12| Years| 2 Y
    1| 9| Years| 1 Y
    0| 21| Years| < 1Y

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. Where do you use the code you posted?
    2. Just out of curiosity, why don't you use a For... Next loop instead of a a Do Until... construction?
    Have a nice day!

  4. #4
    Join Date
    Oct 2009
    Posts
    204
    1. Where do you use the code you posted?
    It is in the Detail_Format area of the report
    2. Just out of curiosity, why don't you use a For... Next loop instead of a a Do Until... construction?
    Because I'm not that good at writing loops, and this is what I know

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Personally, I would rewrite the procedure like this:
    Code:
        Dim i As Integer
    
        With Me.Graph38
            For i = 0 To DCount("[YearNum]", "qry_EmployeeTenure")
                Select Case DLookup("YearNum", "qry_EmployeeTenure", "[CountOfEmployeeName] = '" & .SeriesCollection(i) & "'")
                    Case "< 1Y":    .SeriesCollection(i).Interior.Color = RGB(220, 230, 242)
                    Case "1 Y":     .SeriesCollection(i).Interior.Color = RGB(185, 205, 229)
                    Case "2 Y":     .SeriesCollection(i).Interior.Color = RGB(149, 179, 215)
                    Case "3 Y":     .SeriesCollection(i).Interior.Color = RGB(79, 129, 189)
                    Case "4 Y":     .SeriesCollection(i).Interior.Color = RGB(37, 64, 97)
                    Case Else:      .SeriesCollection(i).Interior.Color = RGB(37, 64, 97)
                End Select
            Next i
        End With
    The main advantage is that you only call the DLookUp function once for every item in SeriesCollection().

    However, this will not solve the problem. Is [CountOfEmployeeName] numeric? If yes, you need to drop the quotes in the criteria of the DLookUp function:
    Code:
    If DLookup("YearNum", "qry_EmployeeTenure", "[CountOfEmployeeName] = " & Me.Graph38.SeriesCollection(i)) = ...
    or in my version:
    Code:
    Select Case DLookup("YearNum", "qry_EmployeeTenure", "[CountOfEmployeeName] = " & .SeriesCollection(i))
    Have a nice day!

  6. #6
    Join Date
    Oct 2009
    Posts
    204
    Thank you Sinndho, that gave me a starting point to get it working.

    The code kept highlighting this line:
    Code:
    Select Case DLookup("YearNum", "qry_EmployeeTenure", "[CountOfEmployeeName] = & .SeriesCollection(i) & ")
    I tried to troubleshoot with message boxes displaying the value of the .seriescollection, but I kept getting "Run Time error 438 - Object doesnt support this property or method." Then it hit me: I can only change the format of the series (I think), not call the value.

    I changed the code to this, and it works!
    Code:
    Dim i As Integer
    
    For i = 1 To Me.Graph38.SeriesCollection.Count
        With Me.Graph38.SeriesCollection(i)
            Select Case i
                Case "1": .Interior.Color = RGB(220, 230, 242)
                Case "2": .Interior.Color = RGB(185, 205, 229)
                Case "3": .Interior.Color = RGB(149, 179, 215)
                Case "4": .Interior.Color = RGB(79, 129, 189)
                Case Else: .Interior.Color = RGB(37, 64, 97)
            End Select
        End With
    Next i
    The only problem now, is the colors look a different shade than they should, so now I have to figure out how to stretch a picture as a fill instead of the RGB values.

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  8. #8
    Join Date
    Oct 2009
    Posts
    204
    I got it to work with filling the series with a picture instead of a color.

    In case anyone was wondering the syntax as I was, here it is:

    Code:
    Dim i As Integer
    
    For i = 1 To Me.Graph38.SeriesCollection.Count
        With Me.Graph38.SeriesCollection(i)
            Select Case i
                Case "1": .Fill.UserPicture ("C:\Documents and Settings\qt62121\Desktop\Ops Files\Pics\Colors\less1year.bmp")
                Case "2": .Fill.UserPicture ("C:\Documents and Settings\qt62121\Desktop\Ops Files\Pics\Colors\2year.bmp")
                Case "3": .Fill.UserPicture ("C:\Documents and Settings\qt62121\Desktop\Ops Files\Pics\Colors\3year.bmp")
                Case "4": .Fill.UserPicture ("C:\Documents and Settings\qt62121\Desktop\Ops Files\Pics\Colors\4year.bmp")
                Case "5": .Fill.UserPicture ("C:\Documents and Settings\qt62121\Desktop\Ops Files\Pics\Colors\5year.bmp")
                Case Else: .Fill.UserPicture ("C:\Documents and Settings\qt62121\Desktop\Ops Files\Pics\Colors\6year.bmp")
            End Select
        End With
    Next i

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Clever! Thanks for sharing.
    Have a nice day!

  10. #10
    Join Date
    Dec 2012
    Posts
    1
    Hi everyone,
    in visual basic, when i create a scatter plot, the program creates data points with different color. Does anyone know how I could get the color that has been assigned to a DATA POINT a to apply that color to the DATA LABELS ?? I would like this to be automatic and not assign RGB manually. Many thanks!

  11. #11
    Join Date
    Jan 2009
    Location
    Kerala, India
    Posts
    183
    www.MsAccessTips.com (Learn MS-Access Tips & Tricks)
    Learn Advanced MS-Access Programming with sample VBA Code.

    All responses are based on Access 2003/2007

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •