How can I add rounded corners and a drop shadow to the code below for a graph.
Code:
Sub AddAICharts()
Dim co As ChartObject, endf%, i%, r$, sname$, suffix
'~~~ Suffix allows the code to be manipulated more easily with changing the sheet name but keeping the Suffix the same
suffix = Array("A", "B", "C", "D", "E", "F", "G", "H")
For i = LBound(suffix) To UBound(suffix)
'~~~ name of sheet is currently set for Objective
sname = "Data" & suffix(i)
How does one select a range with multiple rows and columns, then select the cell in any one corner of that range. What does the "Set" command do?...is it used when an inputbox command is assigning a range instead of a value to a variable
I've just started with VBA coding and I'm absolutely stuck with one problem I have had for weeks!
I litterally spent days looking for an answer on google, unsuccessfully of course. So here I am
Right, so the problem is the following:
I have series of bar charts (up to 10) with some relationships between them, and I want to highlight those.
Here is a screenshot:
or here: View image: Untitled
I know how to move the shapes automatically, etc. Yet, the big issue I'm encountering is that I can't find a way to get the position of the circled points!
I tried every possible way with GET.CHART.ITEM but the best I get is the top of the bar and not the points inside of it...
If you run it type in 3.5. What gets returned from the procedure is the number 4 NOT 3.5. I think its related to the data type declaration but i thought a data type of long would include the number 3.5.
I beleive it is because the formulas are not rounding the figures to the nearest 2 decimal points. so some costs are 1 or 2 pence out. OK on a small qty not so good on a large qty.
I dont know how to do this as the roundup function only rounds up i need it to round to the neareast 2 decimal points. Also dont know how to integrate it into existing formulas and macros
I want to take the value in I and apply a 10% discount to it and place the new value in column M. Would it be possible to have this new value rounded up or down to the nearest 9.
Example: Column I=7.99 Column M=7.19
Column I=7.59 Column M=6.83 (however I would need it to be rounded to the nearest 9. The price I would need would be 6.79)
Column I=2.19 Column M=1.97 (however I would need it to be rounded to the nearest 9. The price I would need would be 1.99)
I need to plot various data on top of stock charts.
This data could be irregular in date: it could be weekly, or simply random.
To plot this data by itself requires a Scatter Chart.
From what I know so far, you CANNOT DO THIS.
However, I suspect this could be done if I build a Stock Chart from scratch using a Scatter Chart.
Error bars can be used to make the tails, however, I don't know how they built the body bar which has the characteristics of a bar (border, and interior.)
But since I don't really need those two characteristics, I just need a wider error bar line that is provided in the chart edit window. I'm guess through a macro, there are wider line widths assignable.
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 looking for a creative way to display a pie chart within a data point marker of a line chart.
My database has 3 value columns, Type1, Type2 and the Total (Type1 + Type2) these are recorded per day (Date, in Column A)
I have a line chart that displays the total by date, but I want to find a way to display the percentage split of a particular day by type.
I was thinking to load the chart image into the Data point marker, but i don't think that is the best way as the data is updated daily and I would have to do it each day for a few line charts.
The other way I was thinking about was to have a generic Pie chat in the Line chart (Maybe in a corner) and the pie would update depending on way date series was selected or Mouseover'd)
the way that I am approaching it at the movement (Not the best way and by far not the coolest way. Is to have a list of all the dates in a column next to the Line chart and using some VBA, what ever date is selected in the column the pie chart displays the corresponding data. But eh challenge is that when there is alot of dates, I am going to be scrolling up and down.
I am using Excel 2010, but I cant not use the slicer's as the other users do not have 2010, they have 2007.
I have a spreadsheet created in Excel 2003 (which is what we use at work, unfortunately).
My employees periodically take a test to ensure they have certain items memorized (or are making progress to that end). The spreadsheet rows show all 46 of my employees, and their test scores. The columns are the dates that the tests are administered. I can create a line graph based on the chart data, and interpolate these data with no problems.
The problem is that there are 46 employees! 46 lines on the same graph make for a very cluttered, hard to understand visual. I want to simplify the view by "filtering out" some of the data.
I have an additional column in my spreadsheet for each employee's work area (Area 1, Area 2, etc), and another column with data based on first letter of last name (the values here could be "A-G", "H-M", "N-S" and "T-Z", for example). I figure i could filter my line graph based on these two columns. For example, somehow select just Area 1, and reduce the number of lines on the graph to 16. Or better yet, Choose "Area 2" AND "A-M" and end up with 7 employees (and therefore 7 lines on the graph).
Here's what i have tried:
1) Select the work area column, and use the Filter, which created a drop-down list at the column heading. When i use this drop-down list, i can easily filter the data in the worksheet by Work Area, but this is not reflected in the line graph, which still shows all 46 lines. The problem was that i forgot that i had set the Calculation Options to "Manual". Setting this to "Automatic" (or leaving it on Manual and pressing F9) solved the problem, as the chart now updates when i use the filters. Calculation options are under the "Formula" tab in 2007, or in Tools -> Options -> [either calculation or formula, i forget what it's called] in 2003.
2) Create several separate line graphs in several separate sheets. I wouldn't want to assign someone else the task of maintaining a spreadsheet of such inefficient design.
I have created a population pyramid and want to add another chart on the secondary axis which I can do. The part which I seem not to be able to complete is getting the line chart secondary axis to read from the primary y axis. It seems to add a new axis, even when I delete this the line chart does not read correctly.
How can I add missing values to the axis in a pivot chart line chart? I have a numerical X-axis with values such as 0,1,5,8,14, etc. I have another set of Y values that correspond to the X values. If this was an XY-scatter plot I'd be able to plot X vs. Y and connect the dots for "gaps" in the X values. Since it's a pivot chart I cannot use an XY-scatter plot, I can only use a line chart. The line chart doesn't give me the ability to add the "missing" values, so it gives the impression that my data is more closely packed than it is. The data source is external to my spreadsheet, so I cannot add the values before creating the pivot table/chart.
I am trying to combine data from multiple worksheets and make a chart. I have about 200 keywords in every worksheet (about 50), and some of them repeat themselves through worksheets and some don't. For every keyword, I have an associated value in the next column that I want to portray over time (each worksheet is for a different period).
So what I need to figure out is how to be able to pick any 10 keywords from the worksheets and put them in a line chart where I can see the associated value for each period for every worksheet so I can compare my keywords' efficiency. The tricky part is that some worksheets do not contain the keyword and other worksheets contain the keyword in a different cell than the previous wsheet.
I would like combine (overlay) a bubble chart with a connected point scatter chart. I understand that, without VBA, this is not possible. However, I understand that, by using VBA, the markers of a scatter chart can be configured as circles with their size proportional to values in a specified column. This pseudo-bubble chart can then easily be combined with a connected point scatter chart.
My question is: does anyone have any VBA code to share that shows how to configure a scatter chart as the type of pseudo-bubble chart described above.
I am trying to overlay a line chart on top of a stacked column chart. The stacked column chart is a chart where the x axis is dates and the y axis is amount. The line chart is a projected amount that I will reach in few years; the x axis is the date while the y axis is an amount. I can get a chart that has a line and stacked columns, but ever time, the dates get messed up. The line has an extremely wide date range while the columns only have about a month of data.
Is there a way to display only the current month and the past 11 months in a Line Chart in Excel? So if I was to print Jan 2008 Excel graph, it would only display Feb 2007 - Jan 2008 data charting.
The spreadsheet of the data contains data for Jan 2005 - Jan 2008 so far, but only the current month with the past 11 months should display in the line chart depending on what is the current month.
My question is regarding changing properties in a chart without activating it. My current example is with adding data labels, but there are many other instances I could use this information in. Here is my current code:
I get the "Object doesn't support this property/method" error. Is there a way to do this? It just seems inefficient to have to activate the chart in order to make changes.
In the attached file I have the data which shows me the market size in value, my market share in value & I did a calculation to get the percentage. What I need is a chart that shows me the market size in column graph, my market share in line graph and I want the percentages to be showing also.
I add Data Labels and select the percentages. This shows at Cat 1 having 12.5% of the sales (100 of 800 sales), Cat 2 having 12.25%, and so on..... However, what you don't see is the following data:
So based on 1,088 sales, Cat 1 at 100 is actually only 9.19% of TOTAL sales.
I understand what is happening, the chart is giving the percentage based on the data inputted into the chart.
I did consider adding an 11th category to the chart, which was the total of Catalogues 11-15. Whilst this does give a more accurate percentage figure (as now all sales have been inputted), it makes the chart look ****
Is there a way of either adding the 11th category, and then "hiding" it, so that the chart doesn't display it, but uses it's value to calculate Catalogues 1-10s percentage?
BTW: The reason I ask is that I could be dealing with anywhere from 30 to over 100 catalogues. So it's not ideal showing the others in 1 chart.
I have a pareto chart(Bar & Cumulative Line Chart) that I would like to have the line orginate at x = 0 & y = 0 without changing the position of the other points on the line chart.
Basically this is for better visual appearance. When I add a 0 to the table, it skews the alignment of the line chart with the corresponding bar chart.
I would like to combine / overlay a bubble chart and scatter chart with straight connectors.
I understand that, without VBA, it is not possible to combine a bubble chart with a scatter chart.
Unfortunately, I am a VBA newbie, and so I cannot write my own code (though I can usually adapt code to my specific environment).
My question is: does anyone have VBA code to share that will convert a scatter chart series (x,y data in 2 columns) to a bubble-style chart (bubble radius in 3rd column)?
Attached is the sample data worksheet. Chart 1 is XY type chart using Seconds (2nd column of sample sheet as x-axis from 42510 to 42530). How do I change it to Line chart using Time (1st column of sample sheet as the X-axis) retaining same data from 42510 to 42530 on both primary and secondary axis?. And how do I again change it back to XY chart?
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.
I'm looking for the excel model object chart and class diagram. I find the model object's description in the microsoft page, however i'm liked to have the object model and the class diagram in chart or picture.
I got a set of data which is only 2 colums collecting data problems. The output i get is a time stamp and a fault. Im looking to set a chart up with the time been on the X - axis and number of problems per hour on the Y - axis.
I want to use a macro to change attributes of a series in a chart. Unbelievably, if the series has been pasted into the chart from another chart, and although the macro can address the series and even return values (eg name) correctly, the selection simply cycles to one of the original series on the chart.
I have a two bar chart (red and blue) I'm trying to turn into a stacked chart. It has total cars and broken cars. There are a 100 total cars (10 broken). But when I stack them, it shows 110 total cars (it's adding instead of merging them). Once stacked, there should still be 100 total cars....but the tip of it should be red to show what amount is broken. It's adding the two together but it shouldn't....the number of broken cars is still part of the total.
I'll attempt to explain what I'm hoping to do. I have a column of data that I want to plot out into a chart. For example the data points might run from -1.50 to 2.5.
Let's say there are 20 data points in the column and they are spaced thruout the range shown above. They tend to cluster amoung themselves into 2 or 3 distict groups.
I want to first produce one horizontal bar that runs from the -1.50 to 2.5 overall range and secondly, I want to overlay a scatter chart horizontally over the bar showing where the data points lie within the range and also so I can visually see the clusters of points.
With the data residing in a column, when I generate the scatter chart, the points are laying verticle within the chart and spreadout horizontally. I can manually get what I want by narrowing the chart down to the point that the points lie on a straight vertical line, but I haven't been able to figure out how to "rotate" the chart 90 deg to overlay the scatter points within the bar chart.
Naturally, the optimum solution would be such that I could generate the charts originally and then allow the spreadsheet to generate the new charts as we modify the data values, etc.
