[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


MS Graph with Access 2000, Changing Column Colors

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

Expert Comment

ID: 12530545
Yes :)
LVL 58

Accepted Solution

harfang earned 375 total points
ID: 12530565
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

Expert Comment

ID: 12530682
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



Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

834 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