I have a chart with 5 different series. Instead of editing each one, say the marker size, is there anyway I can change the marker size for all of the different series at the same time?
View 1 Repliesi have a chart on a worksheet, it contains multiple series (3) in which i wish to change the values/ range of each series of the chart through vba. Thus far i have struggled due to the lack of intellisense, in which i tried
Worksheets(1).ChartObjects(2).Chart.Series("Fails").Values = Range("b12:b14")
with "Fails" being the name of that particular series.
I just joined ozgrid and this is my very first post. I have read the rules but spare me if I make some errors.
My question is related to plotting time series charts using VBA. I have data in the following format.
04/21/08 9:04:47 4.5055
04/21/08 9:04:48 4.5055
04/21/08 9:04:49 4.5055
04/21/08 9:04:49 4.5055
04/21/08 9:04:50 4.5055
04/21/08 10:05:00 3.5055
04/21/08 10:05:32 3.5055
04/21/08 10:05:40 3.5055
04/21/08 10:05:48 3.5055
04/21/08 10:05:51 3.5055
04/21/08 11:07:59 5.5055
04/21/08 11:09:00 5.5055
04/21/08 11:10:01 5.5055
04/21/08 11:11:02 5.5055
04/21/08 11:13:08 5.5055
04/21/08 11:14:11 5.5055
04/21/08 11:15:16 5.5055
The column on the left is NOW() output and the one on the right is data corresponding to the given date and time. You may interpret the above data as snapshots taken at regular time intervals.
Now what I want to do is make a chart with the above data. Where ever there is a break in the snapshots, represented by a blank line, I want the graph to jump to the next data point much as the way stock prices move from the closing price to the opening price on the next day. I am not able to figure how to do that. Since I want the x-axis to represent time, using scatter charts does not work since the intervals are not proportional to the time then.
Is it possible to vary the size of data markers (eg spots) in a chart by their value?
View 2 Replies View RelatedI have a macro that changes the marker style for all series in a scatter chart, chosen through an input box.
[Code].....
It works nicely, but I want it to exclude any series that do not have markers (i.e. lines used to indicate a reference value). I've tried an if-then statement after the With statement, but it will add markers to any line series:
[Code] ......
Basically, if the series has markers or line+markers, I want it to change the marker style, otherwise do nothing with the series.
i am trying to add 50 new data series and it takes hell of a lot of time to do it manually.
Sub Macro6()
Dim total As Integer
Dim Taper As Integer
Taper = 2
Do Until total < 100
total = total + 1
Taper = Taper + 1
ActiveSheet.ChartObjects(" Chart 3").Activate
ActiveChart.PlotArea.Select
ActiveChart.ChartType = xlBubble
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection("Total").XValues = "=Data!R" & Taper & "C6"
ActiveChart.SeriesCollection("Total").Values = "=Data!R" & Taper & "C8"
ActiveChart.SeriesCollection("Total"). Name = "=Data!R" & Taper & "C9"
ActiveChart.SeriesCollection("Total").BubbleSizes = "=Data!R" & Taper & "C7"
Loop
End Sub
I have a chart with coloring for specific series sets. When I create a similar chart, can I copy the formatting from one range to the other? Why does it default to certain styles?
View 1 Replies View RelatedI have a combination column and line chart with the line series on the secondary axis. Is there a way that I can tell excel to scale the secondary axis so as the line always appears above the columns? As it is it appears above in most instances but sometimes appears below the columns.
View 2 Replies View RelatedThe charts I received have many Series formulas that reference and external spreadsheet
Is there a way that I can modify them and reference the same sheet name but locally
For your information
When selecting menu Formulas and then "Show Formulas" chart data series do not display
When selecting a Chart the "Show Formula" selection is not available (at least I did not find it)
In addition, when selecting the Chart, Then Design, then "Select Data", it replies in the pop-up with the comment "The data range is too complex to be displayed...."
There are about 40-60 formulas as shown below!!
The sheet name with data is D_Brand_Q
HTML Code:
' here is a sample of the current formula
=SERIES('C:2011-12-Jul-Nov[2011_06_Sales.xls]D_Brand_Q'!$CC$1,'C:2011-12-Jul-Nov[2011_06_Sales.xls]D_Brand_Q'!$E$197:$E$218,'C:2011-12-Jul-Nov[2011_06_Sales.xls]D_Brand_Q'!$CC$197:$CC$218,2)
' This is the desired formula
=SERIES(D_Brand_Q!$CC$1,D_Brand_Q!$E$197:$E$218,D_Brand_Q!$CC$197:$CC$218,2)
I have used named ranges to make a chart automatically adjust to a changing list of products in one column, but I do not know how to do this with multiple series. If I want to add products to this table, how can I make my chart dynamically adjust multiple series?
So the chart range is currently E5:H10. But I would need it to go beyond row 10 when I add new items.
Sheet1
*EFGH5*Week 1Week 2Week 36Product 11,234,123 1,258,805 1,283,982 7Product 21,234,223 1,258,907 1,284,086 8Product 33,423,321 3,491,787 3,561,623 9Product 4342,121 348,963 355,943 10Product 52,343,424 2,390,292 2,438,098
Excel tables to the web >> Excel Jeanie HTML 4
I have a xy chart with two sets of data, New and Reference. I want New to be the first entry in the legend and to show up on top when the two lines overlap.
Is it possible to independantly change the width of donut chart series? I want the outer ring of the donut to be slimmer than the inner ring.
View 4 Replies View RelatedAny way to be able to toggle (checkbox) a particular series in a chart.
I found this old thread that shows a simple method: [URL]
BUT, I do not want the series to appear in the legend if it is not plotted.
I would like to display the latest data label value for a data series So if I update my table for period 2 the chart should update to display only this value. Then period 3 and so on Workbook attached
View 3 Replies View RelatedI am applying for a job as an executive assistant and before the interview I have to complete a "test." I am having trouble with one direction. Here is the question:
"10.Apply series name and legend key in data labels to the Star data series. "
I have included what I think is the correct answer, but I am not sure. It is under the column chart sheet.
I'm trying to use VBA to automatically change the data displayed in an excel chart. To do this I count the number of items displayed on the chart using Chart.Seriescollection.Count. I then loop over the Chart.Seriescollection(x) and try to change the Chart.Seriescollection(x).Formula string to link to the cells I want.
Problem is that, sometimes, the cells the curve is currently linked to are empty. Such a curve does show up in the Count, but I can't access the .Formula, even though this can be done manually from within Excel.
I am faced with the task of reducing the size of a group of userforms because of screen size limitations.Some of the forms are quite complex and I am wondering if it is possible to select the form and all its contents and reduce it proportionately. It is possible to do this with the form frame but I have not found a way to include the controls.
View 3 Replies View RelatedIs it possible to make a worksheet smaller. What I want to do is to be able to tab from, a1 to b1 to c1 to d1 etc up to g1 and then if I tab again it goes to a2. Is this possible? Is there any way of making it skip the a coloumn when tabbing even if it has data in it?
View 10 Replies View RelatedI've increased the size of my combo box but the text remains in size 8.
I've tried to click on the box and open the properties but i can only get the sheet properties...
I have a created a filter in my workbook. Text for the entire spreadsheet is set to Arial | 12 pt. Font. However, when I select any one drop down list the text therein is shown in a font size that is too small. How can I change the font size to make it more readable?
View 2 Replies View Relatedim using combobox from the forms toolbar. and i have tryed and searched lots of forms and sites but no results, so what im trying to do is change the font size to 12 in my combobox using VBA.
-im using xl2000
-i dont wanto switch to the combox in the active x becouse i have more than 70 comboboxs in this spread sheet. and it will also be very complicated to change the comboboxes due to all the link cell's and there values.
I have created a dynamic chart in excel where either the number of Series or the number of Data Points will change dependent on user inputs in the model. I've done this using the Offset Function in a Defined Name in Excel and dropped this Defined Name into vba code using ActiveChart.SetSourceData.
Anyway, all works fine, pulling the correct data, etc. What doesn't work is the final appearence of the chart. The chart has a Legend placed at the bottom of the chart, as standard, unless there is only one Series, when I remove the legend altogether. Whilst the legend works fine, the Plot Area of the chart doesn't update automatically to accomodate the changing size / existence of the Legend. Does anybody know how to do this? I tried using hieght, etc properties, but I don't know how to make them variable
I have a spreadsheet which reports across different categories, but there's a lot of info. So simplify this, I've written a simple macro that changes the series range in an existing chart, so top level and by category. This works well apart from I'm using excel 2007, and some people aren't, so the default colours look fine on my version, but change to some awful colours on anyone with an earlier version of excel. I need a way of changing the colours to standard ones depending on which macro is being run.
I have tried running the following, but I get an error, basically saying the series hasn't been selected.
ActiveSheet.ChartObjects("Chart 6").Activate
ActiveChart.SeriesCollection(3).Select
ActiveChart.SeriesCollection(3).Interior.Color = RGB(255, 0, 0)
I've been successful at changing the font size for the Main title and legend using these commands:
Code:
ActiveChart.ChartTitle.Font.Size = 8
ActiveChart.Legend.Font.Size = 8
But the horizontal axis has me stumped. I recorded part of this but can't get it to work:
Code:
ActiveChart.Axes(xlValue).AxisTitle.Font.Size = 8
How can I change the typeface to Times New Roman for all three and change the font size for the Axis titles? hundreds of charts to change and not enough patience to to it manually.
I'm trying to change 115 charts so that the Xvalue axes (Y-axes) will start on the lowest value of my data series. This is what I've come up with so far:
[Code] .....
What is the syntax to set instead of X so that all Xvalue axes is set to the lowest Data series value in individual chart?
I have a series of numbers in one column. Is there some way I can divide all the numbers together by, say 10, without doing it manually?
eg.
12
15
15
25
20
should become
1.2
1.5
1.5
2.5
2.0
Is there a way in Excel to do that?
I'm trying to change the default option for the data series when graphing in Excel 2003 .... I am trying to create a line graph
Excel, by default, is putting static pressure on the x axis and the air flow on the y axis .... I need it to be the other way around
I have found out that:
By default, Excel plots whatever you have fewer of—rows or columns—as the data series
apparently it is possible to change this Excel default option, but I can't figure that out
in step 2 of the Chart Wizard, I have tried many different things, but can't get Excel to chart the Air Flow CFM on the x axis, and have static pressure values on the y axis
I still get the 'Object doesn't support this property or method. Error code 438' error. I am using Excel 2010
VB:
j = 3
Do Until j = 6
ActiveWorkbook.Sheets(k).Activate
Set chartX = ActiveSheet.ChartObjects("X")
[Code]....
The macro is supposed to dynamicaly adjust series range for 3 charts for x,y,z, values in each of sheets. Charts are a line type.
I have a small problem using the ‘sumproduct function’ which I am using it to calculate the area under curves using the following equation (=SUMPRODUCT((B10:B109-B9:B108)*(D10:D109+D9:D108))*0.5). However I have hundreds of curves to calculate the area under and the length of the data series for each curve is different, I am currently changing the length of the data series by hand. I have attached an example worksheet of 2 sets of data (the formula I am using at the moment is highlighted in blue – row 203). Is there are function or formula that I can use inside this equation to change the range of the formula depending on the length of the data series?
View 2 Replies View RelatedIn A1 I have 35 min elapsed time and need to change it to time as a portion of an hour. It reads the 35 min as TIME, therefore I am currently using
=IF(A1>=0.5,A1-0.5,A1)*24. Output is .58, which is perfect.
(output column formatted as a number)
Likewise 3:28 becomes 3.47.