If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
build chart template; then add series one by one
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 Whe=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 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.581 / Virus Database: 368 - Release Date: 2/9/2004 |
#2
|
|||
|
|||
build chart template; then add series one by one
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 ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ L Mehl wrote: 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 Whe=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 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.581 / Virus Database: 368 - Release Date: 2/9/2004 |
#3
|
|||
|
|||
build chart template; then add series one by one
Jon --
Thanks for the guidance and simplified code. I'll get to work. Larry "Jon Peltier" wrote in message ... 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 ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ L Mehl wrote: 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 Whe=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 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.581 / Virus Database: 368 - Release Date: 2/9/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.581 / Virus Database: 368 - Release Date: 2/9/2004 |
Thread Tools | |
Display Modes | |
|
|