Chart Formatting VBA

[riedyp]

I have a chart labeled "Time Spent versus time allocated" and it has a code that will update the chart when new data is entered.
However from looking at the bar chart you can see that the formatting of the bars and the labels are not quite right.
Any idea of how i can set the formatting through VBA?

Below is the code being used:

Private Sub TimeSpent()
Dim ch As ChartObject
Set ch = Worksheets("Executive Summary").ChartObjects("Chart 8")
LastRow = Worksheets("Executive Summary").Columns("J").Find(1, SearchDirection:=xlPrevious, LookIn:=xlValues, LookAt:=xlWhole).Row

Worksheets("Executive Summary").ChartObjects("Chart 8").Activate

    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.FullSeriesCollection(1).Name = Worksheets("Executive Summary").Range("L34")
    ActiveChart.FullSeriesCollection(1).Values = Range(Cells(35, 12), Cells(LastRow, 12))
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.FullSeriesCollection(2).Name = Worksheets("Executive Summary").Range("M34")
    ActiveChart.FullSeriesCollection(2).Values = Range(Cells(35, 13), Cells(LastRow, 13))
    ActiveChart.HasLegend = True
End Sub

 

[Jon Peltier]

Looks like you have a lot of empty series in the chart. There are only two sets of visible bars, the blue and orange ones. But I see legend entries for Series 3 and 4, and the gap between each pair of visible bars is huge, enough for a couple dozen blank bars.

Not surprising. ActiveSheet.SeriesCollection.NewSeries adds a series. So you've been adding two series every time the program runs, and then changing the name and values of the original series 1 and 2. Delete those two lines of code, and delete all the excess data series in the chart.

 

[riedyp]

Okay thank you that worked.