Format Charts Upbars, Downbars And Droplines

Feb 14, 2008

I have a macro that charts data as line charts. I wish to format droplines and updown bars to different colors. The challenge is that some charts do not have upbars and others do not have down bars. Is it possible to skip? The formatting code I used was:

Sub Formatter()
With ActiveChart
With .ChartGroups(1)
.HasDropLines = True
.HasUpDownBars = True
.GapWidth = 90
End With
.ChartGroups(1).UpBars.Select
With Selection
.Border.ColorIndex = 5
.Interior.ColorIndex = 5
End With ........................

View 2 Replies


ADVERTISEMENT

Format Multiple Charts Via VBA?

Jul 28, 2013

I have 23 Charts on a sheet which I place into a MS Word Doc. ATM all charts have borders, which is what one of my Managers like, however another Manger likes these charts without borders. I tried to record a macro so I could have this done by VBA (excel 2007) but it did not record the formatting. write a code to remove a border and and then add a border back in specifying colour, border styles (width etc). Say just for Chart 38, I can the just repeat this. But here are all the chart numbers in case you need them: 38,20,27,5,11,21,13,6,9,19,10,7,28,29,30,31,32,12,33,34,37,35 & 36.

View 5 Replies View Related

Conditional Format Of Colors In Pie Charts

Jan 18, 2008

I want to conditionally format the fill colour of pie or donut segments based on either the value of the segment (e.g. red for negative, green for positive), or based on the value of related cells (e.g. Equal sized segments coloured red for related %age value >40, amber for 40 to 60, green >60).

View 5 Replies View Related

Excel 2007 :: Format Multiple Charts In One Sheet

Apr 21, 2014

I can run this code successfully:

Code:
With ActiveSheet.ChartObjects("Chart 2").Chart
.Axes(xlCategory).TickLabels.Font.Size = 20
End With

But this code throws a "This Object Is No Longer Valid" error when it gets to the first .Axes line:

Code:
Sub ChartFormat()
'
' Format Charts macro
'
Dim ch As ChartObject
For Each ch In ActiveWorkbook.Sheets("Summary").ChartObjects
With ch.Chart
.Axes(xlCategory).TickLabels.Font.Size = 16

[Code] .......

I'm using Excel 2007.

View 1 Replies View Related

Copy Charts From Excel To PPT - Unable To Resize And Format Chart

May 23, 2014

I have an excel which creates PPT from the excel. but the graph pasted in PPT are not aligned and formatted.

By aligned, i mean re-size the graph and paste in a formatted fashion (currently they are overlapping each other)

My entire PPT created is off alignment and formatting. E.g heading are not properly aligned (they are touching the line below) and so on.

View 1 Replies View Related

Converting Static Charts To Dynamic Charts

Jun 27, 2013

My DB is in table format . I use this table as source data for 2 barcharts and 1 pie chart.Following are my table headers

Costs|exp heads|Month1|Month2|...|Month n|Spark lines|Average

When i add a month coloumn,Sparklines and Average coloumn should get updated automaticaly.Now this is not happening even if the data is in table format.I also want the graphs to be automaticaly updated.

View 2 Replies View Related

Pivot Charts Versus Charts

Sep 5, 2012

I am trying to put some charts into a report that is pivot table based. I have some code that will work if the pivot tables stay stagnic but the users may change the tables around so that could be an issue of new data. I have the following code where I changed the source to the pivot table name (general name because of numberous report possibilites). I have taken out the other charts because they are just a variation of the chart 1. I am crashing on the line with the * on it.

Sub UWTierChart()
Dim oCell As Range
Dim oChart As Chart
Set ws_data = ActiveSheet
'Chart 1
Sheets(wsPT).Select
Range("B21").Select

[code]....

View 2 Replies View Related

PPF Charts

Jan 12, 2010

I have never done this before and I am having a hard time - it seems that none of the graphs in the Excel menu accommodate what I want and I have no idea what I am doing to boot.. Could someone PLEASE help?

My data is: American & Japanese workers can each produce 4 cars a year. An American worker can produce 10 tons of grain a yr. / Japanese 5 tons of grain a yr.
Each country has 100 million workers.

Need PPF and Opportunity cost of car & grain from U.S and Japan.

I have at this for two days and I cannot get the information in the chart the way it is supposed to be. It makes no sense evry way I have tried it and the graphs don't even look correct. I've tried several line graphs and scatter graphs..

View 11 Replies View Related

Updating Charts In 97

May 23, 2008

I update graphs/charts everyday. I've been looking at ways of updating a lot of charts in 1 action.....

What would the formula be to have the range of the chart (ie =Data!$BS$1188:$BS$1201) to get the row numbers, not the row, from numbers entered elsewhere on a data sheet (ie =Data!$BS$1188:$BS$1201)

As a lot of the time I use the same number range but on different rows and sheets.

View 12 Replies View Related

Selecting All Charts

Nov 26, 2008

I'm trying to find a way to select all charts in a worksheet and pasting them into a new sheet as a jpeg and can't find a way to select the charts all together as a group like if you shift click them.

I'm looking to copy and paste them as a group because they are grouped in a specific way for a report.

View 11 Replies View Related

Delete The Charts

Oct 28, 2009

I have 2 charts on one sheet. Both the charts are generated using 2 different VBA code. I have also created a Button to trigger the code and generate chart when the button is clicked.

I recently realized that I was creating charts upon charts and was making the excel file bigger in size all the time.

So, I wanted to see if I can delete the individual chart before generating a new one.

Currently, I am using the following code to delete the charts.

View 4 Replies View Related

XYScatter Charts In VBA

Dec 3, 2009

I am having lots of issues with generating a scatter chart in vba. I tried recording a macro but the only thing that recorded was my different selection clicks on the chart. No formatting was recorded. I have the following ....

View 12 Replies View Related

Charts In 2010

Jan 11, 2010

if marco recording of chart manipulation has been reintroduced in XL2010

View 6 Replies View Related

VBA And Charts In 2007...

Feb 17, 2010

I have a piece of code that arranges a chart to fit the data it should show. The code used to work perfectly in the old Excel version, but after an "upgrade" to Excel 2007 I get "Automation Error" from the line where the scale type is set.

View 5 Replies View Related

Add-in That CAN Create Big Charts Of 100 X 100

Apr 21, 2013

I don't mind paying for the add-on/add-in, I need to have a chart displayed of 100 rows and 1000 cells of data.

View 6 Replies View Related

Dynamic Charts ..

Feb 24, 2007

I have a spreadsheet that calculates a value for each age up to 65 on a separate row. I have parameterized the spreadsheet so that depending on the age entered by the user, any row below the age 65 calculation row is blanked out (set to "") with an if statement.

After a new age is entered I would like a line graph to compare the values. My problem is that if I select all of the possible data values (I assume none are less than 20, therefore there are 45 rows of formulas) any rows that have blank values show up in the chart. My data starts in cell A1.

I would like to come up with a macro that accomplishes the following . . .

- determines the number of the lowest row that has a number value in it (since there are formulas below this, Ctrl down does not do this correctly)

- set a named range starting from cell A1 based on x rows (calculated in the prior step) and y rows (constant)

View 9 Replies View Related

Creating Charts With VBA ..

May 26, 2009

I've got a table which has additives in Column A and products on Row 1. There are 13 additives and 10 products. Each combination of product and additive has a price. So I have 130 different prices in my table. The prices change monthly and the new prices are added to the sheet so that last months prices will move to left so that the newest price data table is A1:J13 and previous month is K1:T13 and so on.

I need to make an individual chart to every combination which contains the history of the price changes. I have two cells (A20 and B20) where the user can pick a product and an additive (created a menu by using Data|Validation...). These selections are combined in a cell C20 (=A20&B20). Then I have a macro that is linked to the cell C20 and is basically Case Is = 1st combination --> Call Macro1, Case Is = 2nd combination --> Call Macro2 etc.

That macro will work out just fine, BUT is there any way I could make all the 130 charts easier? Now I've recorded a macro that creates the first chart and then I've just copy-pasted it, changed the attributes (cell numbers, names and so on) that define the data to the next charts. This will take ages...

Every combination will have at least 13 changes (12 monthly price cells and the name of the cart) so in total this will mean about 1500 manual changes. In my case this will mean a high danger of typos.

I know, it would be SO much easier if I could give you the Excel file to look at...hopefully you understood what I mean.

I'll only follow the changes within a year so prices older than a year will not be shown in the charts.

View 9 Replies View Related

Creating Pie Charts With VBA

Jul 14, 2009

Now I need to create multiple pie charts based on data in tables on many sheets.

I need to create the same set of charts, for each sheet separately.

I recorded a macro for one sheet, which created following

ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlPie
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = "='GALLERY ON 4TH'!$B$2"
ActiveChart.SeriesCollection(1).Values = "='GALLERY ON 4TH'!$D$6:$D$8"
ActiveChart.SeriesCollection(1).XValues = "='GALLERY ON 4TH'!$A$6:$A$8"
Range("A11").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlPie
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = "='GALLERY ON 4TH'!$A$6"
ActiveChart.SeriesCollection(1).Values = "='GALLERY ON 4TH'!$B$6:$C$6"
ActiveChart.SeriesCollection(1).XValues = "='GALLERY ON 4TH'!$B$5:$C$5"
ActiveWindow.SmallScroll Down:=3
Range("A11").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlPie
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = "='GALLERY ON 4TH'!$A$7"
ActiveChart.SeriesCollection(1).Values = "='GALLERY ON 4TH'!$B$7:$C$7"
ActiveChart.SeriesCollection(1).XValues = "='GALLERY ON 4TH'!$B$5:$C$5"

However I cannot automate. I tried different methods for creating the charts, as found on the site [prefer .chartobjects.add(x,y,z,a) since I can immediately position the chart] , but get stuck at seriescollection everytime.

It looks like when the chart is created by XL (with suggested data in it) I cannot address the series data.
I got it right to generate a chartobject that is empty, then added the series data to it, but when opening the new chartobject, it is again populated with suggested data.

How do I generate an empty chartobject everytime, so I can insert seriescollection info with code?

Any other way to generate charts?

View 9 Replies View Related

Word - Charts

Aug 27, 2009

Excel / Word 2003.

I am exploring yet another avenue of vba coding that i am not familiar with.

I have a template word document that i am wanting to fill with items (be it cell values or charts) from an excel file.

I am looking at the coding examples on the net and i am slowly getting there. However i seem to be unable to copy a chart from my excel file and copy into word. I need to be able to specify a specific position on the word document too.

View 9 Replies View Related

Line Charts

Nov 4, 2009

Has anyone seen this problem before;

I used a "MID" formula to extract some data and for some reason the chart I'm trying to use won't recognize the data. Here is a copy of what the data looks like. Non of the vaules will show up on the chart.
DPJE2DD2CE2CJan1.2784.8984.8997.65Feb1.2697.6597.6591.79B10==MID(B$3,8,5)Mar1.3091.7991.79154.7Apr1.28154.7154.7147.6May1.29147.6147.687.78Jun1.4387.7887.78112.6Jul1.39112.6112.6115.2Aug1.42115.2115.293.6 Sep1.3393.6 93.6 138.9Oct1.28138.9138.9NovDec115.6YTD1.32115.6115.6

View 9 Replies View Related

Charts Won't Update

Jan 12, 2010

How do I get my charts to update everytime, the data refreshens...I have my series linked to several worksheets within a workbook.

View 9 Replies View Related

Add New Series To Charts

Nov 13, 2008

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

View 4 Replies View Related

Charts Flickering

Sep 29, 2004

I have lots of charts in a sheet "plots" which is based on data in sheet "data Summary". Sheet "plots" have the following code, which is based on the event "actiavte". At the end my main macro, I am activating sheet "plots" and all the charts are working fine. After this if I go to any other sheet in the workbook and come back to "plots", all the charts are flickering and the cursor turns to busy mode. I don't understnad why this is happening.

Private Sub Worksheet_Activate()
For Each ChtOb In ActiveSheet.ChartObjects
ChtOb.Chart.PlotVisibleOnly = False
Next
ThisWorkbook.Worksheets("plots"). Range("e1").Activate
On Error GoTo Handle
With ActiveSheet.ChartObjects("s21max").Chart.Axes(xlCategory, xlSecondary)
.MinimumScale = Application.Average([s21high]) + -4 * Application.StDev([s21high])
.MaximumScale = Application.Average([s21high]) + 4 * Application.StDev([s21high]) + Application.StDev([s21high]) * 0.25
.CrossesAt = .MinimumScale..........................

View 2 Replies View Related

Charts To PowerPoint

Jun 9, 2005

I need to export serveral charts to several slides within PowerPoint from Excel Using VBA. I use 2002 versions. I added chartobjects (2) under 1 but not sure if that is the correct placement to start adding more charts to slides.

Private Sub CommandButton1_Click()
'Sub PowerPointOLEAutomation()
Dim ppt As Object, pres As Object
'Create a Microsoft PowerPoint session
Set ppt = CreateObject("powerpoint.application")
'Copy the chart on the Chart Labels Demo sheet
Worksheets("sw dr").ChartObjects(1).Copy
'Worksheets("sw dr&ot").ChartObjects(2).Copy
'Create a new document in Microsoft PowerPoint
Set pres = ppt.Presentations.Add.........................

View 4 Replies View Related

Trendlines In Charts

Jul 18, 2006

Excel Chart can fit various trendlines in a given set of data. For example we can get linear, as well as exponential trend line for same data set.

How do I decide which trendline best represents the trend in data? Shoud R Sq be used as parameter to see the best fit?

View 9 Replies View Related

Autoshapes In Charts

Feb 19, 2007

I have a chart which has Autoshapes linked to other cells in the spreadsheet above each bar on the chart. Is there any way of making them dynamic, so that if the value of the bar changes, the Autoshape will move up or down with that bar?

View 4 Replies View Related

1 Chart With 2 Pie Charts

Mar 14, 2008

Is it possible to build 1 chart that holds 2 different pie charts? If possible I need to do this with VBA. The pie charts will have the same "components" the only thing that would vary is the percentage of each component (some values will be zero in one of the charts).

View 2 Replies View Related

Create Multiple Charts With VBA?

Aug 18, 2012

I'm trying to create multiple Charts with VBA, each chart on a seperate Sheet. The link below works but i need to change it some and i'm stuck. What i'm trying to do is create a new sheet for each new graph, but I need it to create a graph for the 250 associates in column B then plot 52 weeks of their % from the Row their name shows up in. First name is in (B3), his % data is in range (N3:BO3). I have the weeks numbered out 1-52 in (N2:BO2) and i'd like their name to appear in the Tab and on top of the graph. I applied the VBA code from the link below to my sheet and changed what i knew i needed to change, but it debugs. I use to do each graph manually when i had 25 associates but i would like to use this for the whole building now.

[URL]

View 9 Replies View Related

Rolling Charts Not Working?

Aug 15, 2014

I have data that is linked and is updated automatically. I am trying to use named ranges for the rolling data for each area and my date range is 5 cells using the Today()-5,4,3,2,1. The dates are rolling fine, but I cannot get the data to update, it stays the same from day to day.

look at this and see what my error is? cookies and kudos(no password on the unprotect)

View 2 Replies View Related

Overlap In Charts In Excel?

Dec 5, 2013

I would like to know is it possible to overlap the data & view it as Chart format.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved