Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, New South Wales, Australia
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Specific Bar Chart Colours (2003 SP3)

    I have created a bar graph listing product sales. In the data entry screen I can use a tick box to indicate whether a product is from an external supplier or not. On the bar graph I would like to show the bars for ticked products in blue and unticked products in red. Does anyone know how to do this please?
    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Specific Bar Chart Colours (2003 SP3)

    You'll probably need VBA for that. The Microsoft Access 97 Sample Graphs database can be converted to Access 2000 (or later) format, it contains lots of examples of how to manipulate charts in VBA.
    If you'd like specific help, it would come in handy if you could attach a stripped down and zipped copy of your database.

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, New South Wales, Australia
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Specific Bar Chart Colours (2003 SP3)

    I checked out the MS Sample Graphs and took one look and thought, "This is way beyond me!". Yes please, I would really appreciate your offer of specific help. Many, many thanks!
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Specific Bar Chart Colours (2003 SP3)

    I'll take a look at it later on (if nobody else does in the meantime).

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Specific Bar Chart Colours (2003 SP3)

    The following code in the On Format event of the detail section will color the bars:

    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Dim chtObj As Object
    Dim rst As DAO.Recordset
    Dim i As Integer

    ' Reference to the chart
    Set chtObj = Me.chtGraph.Object
    ' Open recordset on the row source of the chart.
    Set rst = CurrentDb.OpenRecordset(Me.chtGraph.RowSource, dbOpenSnapshot)

    ' Loop through the series of the chart
    For i = 1 To chtObj.SeriesCollection.Count
    ' Look up the value of Exterior? for the product
    If DLookup("<!t>[Exterior?]<!/t>", "tblProducts", "ProductName=" & _
    Chr(34) & rst.Fields(i).Name & Chr(34)) = 0 Then
    ' Not ticked - color red
    chtObj.SeriesCollection(i).Interior.Color = vbRed
    Else
    ' Ticked - color blue
    chtObj.SeriesCollection(i).Interior.Color = vbBlue
    End If
    Next i

    ' Clean up
    Set chtObj = Nothing
    rst.Close
    Set rst = Nothing
    End Sub

    See attached version.
    Attached Files Attached Files

  6. #6
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, New South Wales, Australia
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Specific Bar Chart Colours (2003 SP3)

    Thanks so much Hans, that's amazing and works perfectly. It occurred to me as I woke up this morning (Australian time) the same thing might have been achieved plotting external products against the Z or second axis. Looking at your code, it makes sense to me. Thanks again.

Posting Permissions

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