build chart template; then add series one by one

[L Mehl]

Hello --

This chart will plot data imported from one or more text files.

We want 2 buttons on a UserForm which will allow the user to
1 - erase the old chart and build a new chart "structure"
2 - import one series (in text file) at a time and plot it on the chart
(this code works)

"Structure" means:
1 - rectangle representing the chart area
1 - chart title
3 - Y axis major gridlines
4 - Y axis scale = range 0 to 5
5 - Y axis title blank
6 - X axis scale = blank
7 - X axis title ' "Time in seconds"
9 - no legend

I have been able to achieve this result only if I add a 'dummy' series, as
in the code below (mostly from pasted-in macros).

Is there a way to set up the "Structure" code to build the structure without
having to insert a dummy series?

Thanks for any help.

Larry Mehl


Public Function BuildChartStructure()
'erase existing chart
'build structure of chart
On Error GoTo BuildChartStructure_Error

Dim ChtObj As ChartObject
Dim bytCount As Byte
Dim bytCountLoop As Byte

bytCount = 0
Sheets("Plots").Select
For Each ChtObj In ActiveSheet.ChartObjects
bytCount = bytCount + 1
Next ChtObj
If bytCount > 0 Then
'delete the chart
bytCountLoop = 1
For Each ChtObj In ActiveSheet.ChartObjects
ActiveSheet.ChartObjects(bytCount).Delete
bytCount = bytCount + 1
Next ChtObj
End If

'add chart on selected sheet
Charts.Add

'ActiveChart.ChartType = xlXYScatter
'won't work as xlXYScatter - change it below
ActiveChart.ChartType = xlArea
'put dummy data here as placeholder

ActiveChart.SetSourceData Source:=Sheets("Plots").Range("A3:A6"),
PlotBy:= _
xlColumns
ActiveChart.SeriesCollection(1).XValues = "=Data_Series!R4C1:R8C1"
ActiveChart.SeriesCollection(1).Values = "=Data_Series!R4C2:R8C2"
ActiveChart.SeriesCollection(1).Name = "=Data_Series!R1C1"
ActiveChart.Location Where:=xlLocationAsObject, Name:="Plots"

With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Thread Analysis for Transaction Data"
.ChartTitle.Font.Name = "Arial"
.ChartTitle.Font.Size = 12
.ChartTitle.Font.Bold = True
.ChartTitle.AutoScaleFont = False
.ChartTitle.Shadow = False
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Time in
seconds"
.Axes(xlValue, xlPrimary).HasTitle = False
End With
With ActiveChart
.HasAxis(xlCategory, xlPrimary) = True
.HasAxis(xlValue, xlPrimary) = False
End With

ActiveChart.Axes(xlCategory, xlPrimary).CategoryType = xlAutomatic
ActiveChart.ApplyDataLabels Type:=xlDataLabelsShowNone, LegendKey:=False
ActiveChart.ChartType = xlXYScatter
ActiveChart.Axes(xlCategory).MinimumScale = 27

With ActiveChart.Axes(xlValue)
'can't set y scale min
'.MinimumScale = 0.95
'.MaximumScale = 2.95
'.MinorUnit = 0.04
'.MajorUnit = 1
'.Crosses = xlAutomatic
'.ReversePlotOrder = False
'.ScaleType = xlLinear
'.DisplayUnit = xlNone
End With
With ActiveChart.Axes(xlCategory)
.MinimumScale = 27
.MaximumScale = 39
.MinorUnitIsAuto = True
.MajorUnit = 1
.MinorUnit = 0.2
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With

'return to correct chart type
ActiveChart.ChartType = xlXYScatter

Exit Function

 

[Jon Peltier]

Larry -

1. You can add a chart without data. But this chart has no elements, so
you can't do any formatting. Add the first series with real data, then
do your formatting. If you don't want the series to show until the
viewer has seen the empty chart frame, hide the series (no marker, no
lines, no fill, whatever).

2. Do you need to delete the old chart and build a new one? Can't you
just change the existing one? Change all the labels and stuff, delete
all series but the first one, then change the source data of this
series. Then one by one add more series as needed.

3. There's a whole bunch of code that can be replaced with:

ActiveWorksheet.ChartObjects.Delete

4. Use ActiveSheet.ChartObjects.Add instead of Charts.Add (see
http://peltiertech.com/Excel/Charts/chartvba.html for details).

- Jon

 

[L Mehl]

Jon --

Thanks for the guidance and simplified code. I'll get to work.

Larry