MS Graph with Access 2000, Changing Column Colors

Posted on 2004-11-08
Last Modified: 2012-08-14
Can I change the color of each column using VBA?
Question by:BrianLisko
    LVL 58

    Expert Comment

    Yes :)
    LVL 58

    Accepted Solution

    The easiest way to learn how to change a graph by using VB is to start Excel, create a graph, start the macro recorder and then change the graph. The object model seems identical.

    Then, if your graph is called uofChart, Me.uofChart.Object returns a chart object, which you can manipulate... For example:

    Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

        With uofChart.Object.Axes(1)
            .MinimumScale = CDbl(#1/1/2004 12:00:00 PM#)
            .MaximumScale = CDbl(#1/2/2004 6:00:00 PM#)
            .MajorUnit = CDbl(#4:48:00 AM#)   ' or 1/5
            .TickLabels.NumberFormat = "d h:mm"
        End With

    End Sub

    You will have to figure out the Series collection and the Data Points collection (or something like that), but it shouldn't be too hard.

    Good Luck
    LVL 9

    Expert Comment

    Here is some code I use for colouring a graph based on results.
    The code is called from the Detail_Format event

    Private Sub ColourParetoGraph()
    Dim dbs As Database
    Dim intPointCount As Integer
    Dim intPointColour As Long
    Dim qdf As QueryDef
    Dim prm As Parameter
    Dim rstGraphData As Recordset
    Dim intWeek As Integer

        On Error Resume Next
        ' Return reference to current database.
        Set dbs = CurrentDb
        ' Open dynaset-type Recordset object.
        Set qdf = dbs.QueryDefs("qryWeeklyParetoGraph")
        For Each prm In qdf.Parameters
            prm.Value = Eval(prm.Name)
        Next prm
        Set rstGraphData = qdf.OpenRecordset(dbOpenDynaset)
        ' Set current record.
    For intPointCount = 1 To 10
        If rstGraphData("Improved") > 0 Then
            intPointColour = QBColor(12) ' red
            intPointColour = QBColor(10)
        End If
        Graph53.SeriesCollection(1).Points(intPointCount).Interior.Color = intPointColour

    Set rstGraphData = Nothing
    Set qdf = Nothing
    Set dbs = Nothing
    End Sub



    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now