I am trying to change the color of piechart using the code below:
Code:
Sub ColorPieChart()
Dim i As Integer
Dim myCol(10) As Long
[Code].....
Basically, I want each series in my graph to have the colors formulated by myCol. The code above, however, only colors the graph with color given my myCol(1), so it seems as if something is wrong with my loop.
I hope this passes muster. Anyways, I wrote some VBA code to handle an embedded chart in my workbook. I named the chart "Data" but i'm getting some unexpected trouble from VBA. Last time I worked on this set of code, it ran fine. When I try to run it now, it is throwing up an error: "1004 Unable to set name property of series class" The .name function is not working correctly but everything up till that point is working correctly. I have 5 sets of data in this chart also ....
View 9 Replies View RelatedI'm looking for a way to name lines in a pivot chart instead of default numbering eg ActiveChart.SeriesCollection(1). This in order to attach variable lines to y-axis no 2 no matter which selection is made in the pivot.
View 3 Replies View RelatedI am using a bar graph and would like conditional formatting for a series collection. So if the series collection is greater than 30%, the single entry (not the whole series) would turn red. I have attached an image of what I currently have. There is a data validation list at the top which tells the graph what series to use, so the data is always changing.
I hope this all makes sense. Basically, if an entry is above 30%, I want the single entry in the series to turn red.
I am trying to assign a named range to a series on a preexisting chart in a sheet, but I get the error that "unable to set values property of series class". This is the code example that DOESN'T work:
Sub assign_values()
Dim myrange As range
Set myrange = range("a1")
Set myrange = union(range("a5"),myrange)
activechart.seriescollection(1).values = myrange
End Sub
However, this code DOES work:
Sub assign_values()
activechart.seriescollection(1).values = range("a1")
End Sub
I don't see why the first wouldn't work, but the second would work. I suspect it is something to do with the union function. i am trying to go through a set of data and then group certain cells into a named range to be used on the graph.
I have a bar graph with 2 series of data. Anyone knows how I can specify the order in which they appear? code looks similar to the following. The bar graph shows the second series before the first series. How do I reverse the order?
With cht
.ChartType = xlColumnClustered
' first data series
Set ser = .SeriesCollection.NewSeries
With ser
.Values = someValues
.XValues = someValues
End With
' second data series
Set ser = .SeriesCollection.NewSeries
With ser
.Values = someValues
.XValues = someValues
End With
I have a list of data on one sheet and a 'reports' page on another sheet. The reports page has several pivot tables and a pivot chart. I want the pivot chart to format the bars on the chart relevant to the series name. The series names are "R" "A" amd "G" for Red Amber and Green respectively, I want the chart to change the colour of the series so that it is the correct colour ie. "R" would be coloured Red, "A" would be Amber and "G" would be green.
Sub PivotLoader()
Dim Red As Integer
Dim Amber As Integer
Dim Green As Integer
Red = Range("H9").Value
Amber = Range("H10").Value
Green = Range("H11").Value
Range("B8").Select
ActiveSheet.PivotTables("PivotTable4").PivotCache.Refresh
Range("D25").Select
ActiveWindow.SmallScroll Down:=18
Range("B49").Select...........................
I'm using the following code to set the colours of the slices of all the pie charts in my spreadsheet based on each slice's legend's label: ....
View 9 Replies View RelatedI have a data set that I'm trying to present in a pivot chart and am wanting to use one series to color code or weight another series in the chart. For instance, let's say I have different product categories and two metrics for each, a profit margin (expressed as a %) and a revenue figure (expressed as $s). I want the height of individual bars to represent the profit margin but have the level of opaqueness represent the revenue figures...ie the more revenue associated with a product the more opaque it becomes.
View 6 Replies View RelatedI'm trying to format the color of the data series in a graph that was produced from a pivot table. The pivot table contains a count of each traffic light in each project phase. eg.
. Traffic Light
Phase Amber Green Red Total
Initiate 1 3 2 |6
Design 5 3 |8
Implement 3 5 |8
---------------------------------------------
Total 4 13 5 |24
From this I have created a bar graph. (graph does not represent above data). Now, I'm trying to get each data series to represent their own colour. Instead of the default formatting. Cause every time I click on refresh data on the pivot table, it resets it to default.
ActiveChart.SeriesCollection(1).Select
With Selection.Interior
.ColorIndex = 45
.Pattern = xlSolid
End With
ActiveChart.SeriesCollection(2).Select
With Selection.Interior
.ColorIndex = 43
.Pattern = xlSolid
End With........................
it is possible to have the series of a stacked graph to change color based on the source value.
e.g.
i have a stacked cone graph made up of 5 piece (series)
i need each piece colored either green, yellow or red depending on whether its value is large than another
I trying to code a SeriesCollection in vba, below is the code I have:
' ActiveChart.Name = "Chart1"
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).Name = "='Chart1'!$C$1"
ActiveChart.SeriesCollection(2).Name = "='Chart1'!$D$1"
ActiveChart.SeriesCollection(3).Name = "='Chart1'!$E$1"
ActiveChart.SeriesCollection(4).Name = "='Chart1'!$F$1"
ActiveChart.SeriesCollection(5).Name = "='Chart1'!$G$1"
ActiveChart.SeriesCollection(6).Name = "='Chart1'!$H$1"
ActiveChart.SeriesCollection(7).Name = "='Chart1'!$I$1"
ActiveChart.SeriesCollection(8).Name = "='Chart1'!$J$1"
ActiveChart.SeriesCollection(9).Name = "='Chart1'!$K$1"
ActiveChart.SeriesCollection(10).Name = "='Chart1'!$L$1"
ActiveChart.SeriesCollection(11).Name = "='Chart1'!$M$1"
ActiveChart.SeriesCollection(12).Name = "='Chart1'!$N$1"
However it it giving the error "Object denfined or Appication denfined"
I've created one file: Mappe1_results.xlsm
This file ask in another file "Mappe1_ground.xlsx" for "B" and "N". Now one new sheet is created with the name "month-2014" and shows me which Category (B) is how often referred in one month. After that one chart is created for a better representation. This can be done for every month (which month is selected by the user over the button "Auswertung" in Sheet "Tabelle1").
Now I have a few problems / requirements:
1. If one user is using (maybe) Jan as his selected month, in the sheet "Auswertung" should be one duplicated chart of the Jan Chart., with no other (previous) series. Because Jan has no previous conditions (prev. year).
1.1 If one user is using another month (maybe Feb), in the sheet "Auswertung" should be one chart with both series of Jan and Feb and so one (for the other months). So that in "Auswertung" the chart is one comparison over the months. Only Feb/Mar/Apr/May/Jun/Jul/Aug/Sept/Oct/Nov/Dec have one prev. month.
For example: If one user write "Apr" into the inputbox and "Mar" is in the sheet existing, so in the chart of "Auswertung" should be April and March shown.
Some functions are set in my macros, but the problem is that the results are not equal between the sheet "Auswertung" and maybe "Jan-2014" or "Feb-2014". Because in "Auswertung" we need called all Categories (they can be found in "Referenz" - A).
All what I want is in "Auswertung" one chart with all present categories of "Referenz" - A and the series of the created sheets by the user.
2. The next problem is, every created sheet has one legend "Anzahl im ..." - this legend of every series should be shown in "Auswertung", too. So that we know which color is for which month, u know.
I have a VBA module in Access that extracts data and copies to an Excel spreadsheet. Every time I run the code additional data is extracted and displayed on a chart together with previous data. When I enter values to the series collection the first axis be it X or Y is entered correctly but the second axis produces error 1004 'unable to set XValues property of the series class' If I swap the lines of code around the error switches to 'Values' (y axis)
wchart.SeriesCollection(NoCurves).Values = wsheet.Cells(9, 3 + (3 * (NoCurves - 1)))
wchart.SeriesCollection(NoCurves).XValues = wsheet.Cells(8, 3 + (3 * (NoCurves - 1)))wsheet.Cells(a, b))
wchart.SeriesCollection(NoCurves).Name = wsheet.Range(Cells(6, 2 + (3 * (NoCurves - 1))), Cells(7, 2 + (3 * (NoCurves - 1))))
I have a graph with something like 40 series that are being plotted. For one, I create non-standard data labels using VBA. In that code, I refer to ChartObjects(1).Chart.SeriesCollection(34). At some point I deleted a series that preceded it in the "select data" list and the code crashed. Took a lot of digging to learn that SeriesCollection(34) had become SeriesCollection(33).
To avoid this in the future, is there a way to refer to the SeriesCollection by a unique name rather than a sequential number?
How to get the index number of a seriescollection within the activechart user has selected .
I am able access the chart selected by the user by using the activechart function, but next i am trying to access the seriescollection selected by the user, in other words the activeseriescollection thing.
Is there any way to access the active seriescollection of an activechart or any other way to access the seriescollecion of a chart selected by a user.
I googled a lot but what i found that we have give the index no. i.e; activechart.seriescollection(1)......
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'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 have this code that colors the lines in my Line Chart
Sub ColorLines()
Application.ScreenUpdating = False
Set chrt = ActiveChart
'Here you want to put your own graph
vals = chrt.SeriesCollection(1).Values
'These are the values to determine later whether the line moves up or down. In this example, I just colored 1 seriescollection (= one line in your line graph), with an extra loop you'd do all lines.
It is very simple: When the lines goes up they turn black. When they go down they turn red.
The code work, but only if all cell in the series is filled. If one cells is blank the code error. I'm using #NA() in all cells that are blank, so the chart skip that cell and continue from the next cell's value.
I run a large simulation experiment. I have a loop plotting data in excel of a user defined area. Because of the limit of 255 series I have allowed a maximum of 250 simulations (they all need to be plotted). But the length of each simulation is free. I know there is a limit of 32.000 data points in a graph and I have this as a condition too.
If I set the data range to 100 columns and 3000 rows the graph is produced when I plot by columns. (code below)
But if I set the data range to 250 columns and 1000 rows I get the above mentioned error message. Even though I only have 250 series.
After the data is plotted it is the code below that gets the error:
[Code] .....
I have the following code:
Sub Macro5()
ActiveSheet.ChartObjects("Chart 243").Activate
ActiveSheet.ChartObjects("Chart 243").Activate
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection(2).Delete
End Sub
However, if there is no SereisCollection(1) actually present in the chart I get an error. How can I work around this? I will need an IF statement I assume, just not sure what it will look like.
I just recently installed Excel 2007 and I would like to know if it's possible to change all data points of a chart at the same time. In Excel 2003, I would normally hold down shift while clicking on each of the data points to make a global change. However, it appears I cannot do that in 2007.
I would like to display each data point's series name. When I go to Layout on the Excel Ribbon, and click on "Data Labels", and click on "More Data Label Options", the actual Y-axis values are shown for each data point. However, I do not want this - I actually only want the Series Name, but when I uncheck "Value" and check "Series Name" instead (under "Label Contains"), it only changes it for one of the series. Is there a better way, instead of going through each and every single series to make this change?
I'm trying to change the color of a row if there is a date in column "N".
I want it to only color from column "A" to "N" of that row. I have a code but it colors the entire row instead of stopping at "N".
Any code for coloring a, e, i, o and u red when they appear in words on a sheet and color Y green/
View 5 Replies View RelatedThe following code is supposed to produce six series on an xlXYScatter chart. It produces seven with the seventh series being a repeat of the sixth but named series 7.
Sub Chart2()
Dim DataRange As Range
Dim CellString As String 'Stores a cell range in the form "AA27:AB39"
Dim CurrentSeries As Integer
Dim SeasonCount As Integer
Worksheets("Hemisphere").ChartObjects(2).Activate
CurrentSeries = 1 ............
I need this chart to show 3 bar colors based upon values from b5:b14 (data column). This chart is based upon the last 10 draws, so since there are 3 positions, the hit frequency expectancy would be (10 draws/3 positions = 3.33 rounded to 3). So now I would like my bar graph colors RED for any values above "3" in b5:b14 (data column), GREEN for any values equal to "3" in b5:b14 (data column) and BLUE for any values below "3" in b5:b14 (data column).
Attached is a sample chart
I would like to have two series of data using the same X axis (date, formatted in months). The Y axis is in intervals of 100,000.
The first data series is historical (actual) data (i.e. Jan 2009 to August 2011). The second data series is forecast (Sept 2011 - August 2012). So i want the forecast series to start immediately after the historical series. It is a 'line with markers' chart. The key objective is that the forecast data looks visually distinct from the historial series.
Excel version: Excel 2010
OS: Windows 7
I know this has been discussed a number of times, but here is my problem
I have three charts in my workbook. I want to attach a macro so that when the chart is clicked it returns to Sheet - Home. I have using the following:
worksheets("Home").activate. But after I protect each chart and the workbook, and save and exit. When I reload the Workbook it has forgotten the assigned macros and nothing happens.
I am trying to use VBA in Excel 2007 to change bar chart series colors. I have found a few posts that link it to a cell background, but I'm struggling to find one that does the font color.
It would be great if I could change the bar chart series to match the color of the text in the A column, so that if I highlighted the value in A1 and changed the text color to orange for whatever reason, the chart updates the value of 1.2 to an orange bar (see below).
T. A1 B1
Sample 1 1.2
Sample 2 2.1
Sample 3 1.7
Sample 4 5.6
I have a chart with 3 data series. The series are located in columns A, B and C respectively. Series I is a general number anywhere from 0 to 100,000. Series 2 is also a number, but is is devided by series 1. So, if series 1 was 100,000 then series two would be 25,000/100,000 which is 0.25. Series three will always be numerator of series 2 or 25,000 in this example. The numerator, or the 25,000 will never change, so, in my example, series three will be a straight line across the chart because it is always 25,000.
In my example, the .25 is plotted on the left value axes, and series one is plotted on the right value acccess (secondary axis). Series three is just a line in the middle.
My problem is that sometimes the line, series 3, doesn't match the values in both the right and left values axes. So, if series one is 48,000, then series two would be 48,000/48,000 = 1, and series three would be 48000 - the straight line. The third series should be a line touching the 48,000 on the right and the number 1 on the left. It does touch the 48000 on the left, but is below the number 1 on the left.