MSChart: Controlling Datalabels and Markers Placement and Format

[joebb3]

I was having a HECK of a time figuring this out so I thought I'd post it just in case someone else needs it.

I have a standard line chart with that tracks 2 series of data.

Budget (Control Figure)
And
How much spent (Order Value)
over time.

I wanted the Order Value line to track daily, but only wanted a Marker and Data Label MONTHLY.

If I tried to do it from the Properties within Chart, no matter what I did, I would get a marker and label DAILY which equated to two large smudges across my Chart. I was placing markers and labels once a month MANUALLY for the longetst time.

TODAY... I solved the automation using countless serches and bits of info from each search.

Here is what I cam up with:

Dim objchart As Graph.Chart
Dim objSheet As Graph.DataSheet

Set objchart = Me!Graph3.Object
Set objSheet = objchart.Application.DataSheet

Me!Graph3.RowSource = "SELECT (Format([TDate],'DD MMM YYYY')),AVG([TrendOrdVal]) AS [Average Order Value],AVG([TrendCF]) AS [Control Figure]  FROM [StatsCurOrdValChart]   GROUP BY (Year([TDate])*12 + Month([TDate])-1),(Format([TDate],'DD MMM YYYY'));"

'Since its a daily chart over a Fiscal Year, I used 366 as last row in the datasheet
lastrow = 366

'cycle through the datasheet row by row looking for "01" (First day of the month)
For X = 2 To lastrow
        strdate = objSheet.Cells(X, 1) 'Full string like "01 Oct '10"
        If Left(strdate, 2) = "01" Then
            'Order Value Series
            objchart.SeriesCollection(1).Points(X).HasDataLabel = True
            objchart.SeriesCollection(1).Points(X).MarkerStyle = xlAutomatic
            objchart.SeriesCollection(1).Points(X).DataLabel.NumberFormat = "$##0.00"
            objchart.SeriesCollection(1).Points(X).DataLabel.Font.Size = 12
            objchart.SeriesCollection(1).Points(X).DataLabel.Position = xlBelow

            'Budget Series
            objchart.SeriesCollection(2).Points(X).HasDataLabel = True
            objchart.SeriesCollection(2).Points(X).MarkerStyle = xlAutomatic
            objchart.SeriesCollection(2).Points(X).DataLabel.NumberFormat = "$##0.00"
            objchart.SeriesCollection(2).Points(X).DataLabel.Font.Size = 12
            objchart.SeriesCollection(2).Points(X).DataLabel.Position = xlAboveAverage
        
        End If
Next X

It works perfectly... at the "01" of every month, it sets a formated data label above or below the line and places a marker.

I hope someone finds this of use!

Joe