We help IT Professionals succeed at work.

MSChart question 2

philcrouch asked
Medium Priority
Last Modified: 2008-03-10
Ok i have figured out that i need an xy scatter chart to display the information i have (i think)

I have some data stored in my database that run behind my VB front end.  For simplicity sake i will
give explain only fields required.

RA# - agreement Number
StartDate - start date agreememnt
EndDate - end date of agreement

now i have worked out how to display this information out so that i get a visual of the RA# and the dates it happens.

my problem is that the ra# themselves can range from 1 - 10000 (or whatever)

if i was to try and display RA# 20 and 700 on the same chart, it would be so small that you could not see the result

is there a way to just spread the ra# up the y axis evenly and the lable what they are?

do you uinderstand or am i confused myself?

thanks in advance
Watch Question


i have found out that in excell you can do something called a floating bar chart.

is it possible to do one of those in VB with data from a recordset?


This is a link to microsofts online book about vba it might help


Hi philcrouch, I'm surprised to see you post this as an additional 200 point question.  I would have posted it with zero points and a reference to http://www.experts-exchange.com/jsp/qShow.jsp?ta=visualbasic&qid=20184017

Does this mean we are done with the previous question?

Thanks for accepting my answer on the previous question!  You can use Excel charting from your VB program (assuming you have Excel). Are you interested in that approach or do you want to stick with mschart?  


if i can use excel chart that would be fine

it is just i need to get my data from the databse, not static information on a excel worksheet.

or is there a way to put the recordset informatoin onto the datasheet?

thanks for your continued help

You are referring to an ADO recordset, right?

Do you have the recordset already working with the mschart control?


yes i do mean an ado recordset

i do not have it working, but i can easily put it into the garray formular that you had written down for me.

the query is not the problem, it is how to put that information into the excel sheet


OK, I did something a little weird but it seems to work.  I haven't yet figured out how to change the Y-axis labels so I created a set of "invisible" datapoints that are in the same position as the first datapoint in the bar.  Then I used a label off that.  I'm not sure if that is OK for you but I think it looks pretty good (the chart, not the program).

Also, I'm not sure if this is applicable to your situation but I've added logic to change the color of the bar depending on the number of overlapping RA's.  A slider control sets the overlap threshold so you'll need to add a slider control to your form for this to work.  Use the right and left arrow keys to change the slider and the bars should change color when the threshold is crossed.

Option Explicit
'   garray(Datapoints,series)
Dim garray(1 To 2, 1 To 60)
Dim DayCount(1 To 31)
Dim RaLegend(1 To 15)
Const BeginCol = 1
Const EndCol = 2

Private Sub Form_Load()
    Dim day As Integer, ra As Integer
    Randomize Timer
    With MSChart1
        .chartType = VtChChartType2dXY
       ' .AllowSelections = False
        .Plot.Axis(VtChAxisIdX).ValueScale.Minimum = 1
        .Plot.Axis(VtChAxisIdX).ValueScale.Maximum = 31
        .Plot.Axis(VtChAxisIdY).AxisScale.Hide = True
        .Plot.Wall.Brush.FillColor.Set 150, 150, 200 ' blue
        .Plot.Wall.Brush.Style = VtBrushStyleSolid
    End With
    For ra = 1 To 15 ' generate test data
        RaLegend(ra) = "RA#" + CStr(Int(Rnd * 8000))
        garray(BeginCol, ra * 2 - 1) = Int(Rnd * 31) + 1
        garray(BeginCol, ra * 2) = ra
        garray(EndCol, ra * 2 - 1) = garray(BeginCol, ra * 2 - 1) + Int(Rnd * 10) + 1
        If garray(EndCol, ra * 2 - 1) > 31 Then garray(EndCol, ra * 2 - 1) = 31
        garray(EndCol, ra * 2) = ra
        ' create datapoints used for labelling only - no line is displayed
        garray(BeginCol, ra * 2 - 1 + 30) = garray(BeginCol, ra * 2 - 1)
        garray(BeginCol, ra * 2 + 30) = ra
        garray(EndCol, ra * 2 - 1 + 30) = garray(BeginCol, ra * 2 - 1)   ' end point same as begin point so no line
        garray(EndCol, ra * 2 + 30) = ra
        'Debug.Print ra, garray(BeginCol, ra * 2 - 1), garray(EndCol, ra * 2 - 1)
    Next ra
    For day = 1 To 31 ' zero day counts (number of concurrent RA's for each day)
        DayCount(day) = 0
    Next day
    For day = 1 To 31 ' sum number of concurrent RA's for each day
        For ra = 1 To 15 ' for each ra
            If ((garray(BeginCol, ra * 2 - 1) <= day) And _
               (garray(EndCol, ra * 2 - 1) >= day)) Then
                     DayCount(day) = DayCount(day) + 1
            End If
        Next ra
       'Debug.Print day, DayCount(day)
    Next day
    Me.WindowState = vbMaximized
    Slider1.Min = 1
    Slider1.Max = 15
    Slider1.Value = 4 ' causes chart to be drawn
End Sub

Private Sub DrawChart()
    Dim day As Integer, ra As Integer
    Dim ExceedsThreshold As Boolean
    MSChart1 = garray
    ' scan each ra to identify if it contains any days that are over the threshold
    ' and color accordingly
    For ra = 1 To 15
        MSChart1.Plot.SeriesCollection(ra * 2 - 1).Pen.Width = 100 ' set "bar" width
        ' set "bar" color
        ExceedsThreshold = False
        For day = garray(BeginCol, ra * 2 - 1) To garray(EndCol, ra * 2 - 1)
            If DayCount(day) > Slider1.Value Then
                ExceedsThreshold = True
            End If
        Next day
        If ExceedsThreshold Then
            MSChart1.Plot.SeriesCollection(ra * 2 - 1).Pen.VtColor.Set 200, 30, 30 ' red
            MSChart1.Plot.SeriesCollection(ra * 2 - 1).Pen.VtColor.Set 30, 200, 30  '  green
        End If
        'set the "bar" labels
        With MSChart1.Plot.SeriesCollection(ra * 2 - 1 + 30)
            .LegendText = RaLegend(ra)
            With .DataPoints(-1).DataPointLabel
                .LocationType = VtChLabelLocationTypeLeft
                .Custom = True
                .Component = VtChLabelComponentSeriesName
                .VtFont.VtColor.Set 240, 255, 255
            End With
        End With
    Next ra
End Sub

Private Sub Form_Resize()
    If Me.WindowState = vbMinimized Then Exit Sub
    Slider1.Move Me.ScaleLeft, Me.ScaleHeight - Slider1.Height - 200, Me.ScaleWidth
    MSChart1.Move Me.ScaleLeft, Me.ScaleTop, Me.ScaleWidth, Me.ScaleHeight - Slider1.Height - 300
End Sub

Private Sub Slider1_Change()
End Sub


that is excellent

you are brilliant

at first look that is exactly what i am looking for

thanks heaps

i will get back to you very soon


Thanks philcrouch!  My natural modesty is the only thing that prevents me from agreeing with your assessment of my brilliance.

Hi, philcrouch did you ever get an answer to this question?  
Anyone home?



Sorry about that

it took a little modification but i used it all

thanks again

Glad I could help - thanks for the points and the A Grade!

Explore More ContentExplore courses, solutions, and other research materials related to this topic.