Chart Formatting VBA

riedyp

Board Regular
Joined
Feb 13, 2020
Messages
88
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
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
 

Attachments

  • time2.PNG
    time2.PNG
    88.3 KB · Views: 6

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,933
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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

Board Regular
Joined
Feb 13, 2020
Messages
88
Office Version
  1. 365
Platform
  1. Windows
Okay thank you that worked.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,750
Messages
5,574,016
Members
412,563
Latest member
marianmalone2019
Top