I am building stacked XY Scatter charts and having difficulty formatting the y-axis values (which are data series point labels)
Rob Bovey's XY Chart Labeler manages this fine, but I work in a strict corporate environment which does not allow downloads. Also the file will be posted on our web as a tool for all company users to extract and analyze data from corporate databases.
I am stuck on the number formatting of the labels. My file is too big to attach; I have included the code below.
I'm sure there is something very basic that I am missing. Needless to say VBA is a challenge for me.
Option Explicit
Sub Atest()
Dim intR As Integer, intP As Long
Dim sngVal As Single, sngY As Single
Dim dtX As Date
Dim chtTst As ChartObject
Set chtTst = ThisWorkbook.Worksheets("GRAPHS").ChartObjects("Chart 18")
chtTst.Select
ActiveChart.SeriesCollection("Y Axis Values").DataLabels.Delete
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 using this source to add labels to data points in charts:
Sub AttachLabelsToPoints()
'Dimension variables. Dim Counter As Integer, ChartName As String, xVals As String
' Disable screen updating while the subroutine is run. Application. ScreenUpdating = False
'Store the formula for the first series in "xVals". xVals = ActiveChart.SeriesCollection(1).Formula
the code is from ms and works. (for some reason they also declare variable 'chartname' although it's never used, anyway). the full thing here: [url]
i actually have my source data filterable. so depending on the filters chosen, the chart updates itself. so it removes data points in the chart as more filters are used.
the problem is that the labels ignore the filters. the code above just goes down the column to grab the labels grabbing values in order, even if they have been filtered out.
a cumbersome workaround would be to copy the filtered data to another range and use that for the labels. this is neither optimal because i have lots of data or elegant.
My goal, albeit a simple one, is to return and store a single value within a Series. It is the first in the series collection.
Dim MyPoint As Integer MyPoint = Worksheets("My Worksheet").ChartObjects("My Chart").Chart.SeriesCollection(1).Points.Item(1)
However, after i type the period after "Points", no autocomplete listing appears, and when the expression is evaluated, I get the error 'Runtime Error 438 - Object doesn't support this property or method'. How to accomplish the task of retrieve a single data point in a series and 2) Why the Points and Item methods don't seem to work as described in the help documentation
I have been looking for a printable overview of Excel VBA that covers the fundamentals like program structure and syntax, but have been unable to find anything other than pure examples (like many 3rd party sites) or unprintable references (such as Microsoft's help file). If there is a site that covers the fundamentals of using Excel VBA in some depth
I click on one label, and all labels get selected. This is good. But then when I try to grab them to move all of them, they become deselected except for the one the mouse was over. I want to move them all at once.
My friend has set up an excel workbook with a sheet for every month (labelled Jan graphs, Feb graphs, Mar graphs etc) which has 7 pie charts per sheet. The problem is the pie charts are showing the 0% fields and the data labels are overlapping. I have found a macro that gets rid of the 0% labels but I need to repeat the macro for all the 7 charts on each sheet and all 12 worksheets for each month of the year. I am very new to VBA and coding of any type and have looked but can't find the solution.
The macro I am currently using for "Chart1" on "Jan graphs" is below. The charts are labelled Chart1, Chart2, Chart3 etc.
At the moment I've got a chart with 3 series lines on it and each line has 12 points. I need to select the 12th point on each series in the chart and create a label for it. The 3 lines are Budget, Forecast and Actual and often the Forecast line hits the same points as the Budget line in the chart and every time I try to find a way to select the last point on the Forecast line, I end up selecting the budget one because it's the one in the foreground.
I can select the forecast line with the keyboard arrows, but not the last point.
I also try to zoom in to get it but one of my charts has exactly the same forecast line as budget line and I still need the forecast line because the forecast will change in future.
I can delete the budget line then add it back, but this is difficult and one I'd have to repeat many times because I'm always making graphs and labelling the final point in a series is something I'm going to have to do more often.
How do I select the last point on the forecast line if it's directly behind the budget line?
i have created a xlColumnStacked chart using VBA in excel 2000. i would like to place a line in the center of one of the Points(). How can i locate a specific point, for example Points(1), so what i can then "draw" a line in the center
I have an XY chart that I would like to have the user select a single data point in the chart and be able to add a comment in a table corresponding to that point. For example: if the x-values are in A1:A10, y-values are in B1:B10, the user can select a point, say the 4th point, have some code run that will enter a text string into cell C4. I can manage the code to add the text to the appropriate location, but I'm having trouble with 2 things.
First, how can I ensure that the user has selected a single point (as opposed to the entire series or the chart background)?
And second, how can I determine which point is selected? I think I need to determine the point's item number in the SeriesCollection, then offset that number of rows in my data table. But I can't find a way to retrieve that number.
I'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........................
Background: I have a Chart sheet with a combination bar/line chart on it. Data is coming from a "datasheet". There is a combo box on the chart sheet that allows you to choose a specific office whose data you want to view. Pick an office from the dropdown and the chart changes. The data is a count of closed cases by month. The data sheet shows 22 Offices with their monthly data, along with entries for each month of the Upper Control Point, Lower Control Point and Mean based on the nationwide data for that month.
The chart itself uses bars to show the office's case count per month, and I've incorporated 3 additional data series to plot the UCP, LCP and Mean as lines for each month.
I'd like to change the color of the bars to be Blue, Green or Red depending on where they fall in the 3 lines. I sort of have it working, but I'm stuck.
The code below grabs the value of each series point and compares it to the 3 control points and does change the colors. I've figured out how to use the If, next to change from one series point to the next, but cannot figure out how to loop through each of the control points. As it stands now, it looks only at the first month's control point and uses that for each successive month.
I hope this is clear and I've tried to keep it as short as possible while still providing enough information.
Sub chartbarformat() ' ' chartbarformat Macro ' Macro recorded 5/15/2008 by vacoyoungd ' Dim x As Integer Dim val As Variant Dim AveragePending As Range Dim LowerPending As Range Dim UpperPending As Range
I have a set of data listed by country, I need to add a new country to the list and I want to keep everything in alphabetical order. My problem is that adding a new series to the range anywhere but the end throws of the series formatting for my chart. The plotted points on my chart are the countries flag, so when everything shifts down a row the flags no longer represent the proper country. Is there an easy way to add the new country and keep my formatting lined up or will I need to redo the formatting for all the alphabetically lower countries?
I am putting together a graph and my manager wants the companies with a zero value in the bar chart to stand out (0 = really good). The only thing I could think of was making the individual x-axis label's font turn green if value was zero. Is there any way to accomplish this? (Additional: This chart will be updated every month with new data so I do not want to change by hand every month).
This should be pretty simple but I don't know how to do it. When I run the macro recorder, it doesnt record the change. I just want to change the number format of the chart labels from currency to a normal number and visa-versa. Why doesnt this work?
I am trying to creat a code that with take the value of an active cell and depending on this value will assign a backcolour to a label corresponding to the cell. so far so good. I then want the code to offset to the next cell in the range read its value and assign a colour to that cells backcolor.
here is an idea.
range("A1").select for n = 1 to 4 if activecell = "A" then Label1.BackColor = RGB(0, 0, 0) else if activecell = "B" then Label1.BackColor = RGB(0, 0, 255) else if activecell = "C" then Label1.BackColor = RGB(0, 255, 0) end if activecell.offset(0,1).select next n
Firstly I would like the next loop (refering to A2 in this example) to refer to Label2 not Label1 and so on. secondly the example would loop through 4 cells in one row (A1:A4) but I would like the code to apply to several rows ie (A1:D4).
so thats 16 cell and 16 labels. I could code this in a very inefficient way but I am sure ther is a simple method.
I know from searching through the internet you can change the intervals by modifying the minimumScale, maximumScale and MajorUnit fields of TickLabels. What I want to do is change the period of the axis label to a comma. For example,
Is there anyway to be able to bring decimal point forward without affecting the numbers using any formatting, which will still be there even after refreshing the query.
I have an export from a database that I'm bringing into Excel 2010 of about 30K records. Data points are recorded numerically and I have their associated text "value label" (what it would be called in STATA, for example, not sure what it's called in Excel). I want to create various charts/pivot tables with the data and want the labels to be the text label, not the number.
For example, variable ASSIGNMENT has the following possibilities:
1 2 3 4
Here's what each of those "mean" (I have this in another table):
1 - Sick 2 - Overtime 3 - Court 4 - Present
How do I create a chart or pivot table where the labels are "sick", "overtime", etc., and not "1", "2", "3", "4"?
I have a worksheet that is just a list of items with a location listed next to each item. I took that list and made it into a pivot table and would like it to have it list the items with each location it is in displayed across. I have already set it to tabular form in the pivot table options, but if there are multiple locations it lists them vertically and I want them list horizontally for printing purposes. I have attached a screen shot to explain.
Sheet "A" has a column called "FILEPATH" that contains the complete path to a file (eg. "C:mystuffdocumentsdocA.pdf"). There are about 3500 rows of filepaths on this sheet.
Sheet "B" has a column called "DELETED FILES" that contains a list of deleted filenames (eg. docA.pdf, docC.pdf, etc.), one filename in each cell.
I want to highlight the rows on Sheet "A" that contain a path to a file that is listed as deleted on Sheet "B".
I have a pivot table as shown below which is pretty straight forward however I am trying to create a macro that will automatically change the point name to match the point name in column E then copy that resulting dispaly to another sheet then pick the next point name in line and do the same thing and repeat for 50 rows, so my end result will be 50 pivot tables ready for printing. I can do this manually but I am trying to make it automatic...BTW the point names in column E change everyday but the pivot table supports the name changes.
Option Explicit Sub PointName() Dim Ws As Worksheet Dim Rng As Range, Cel As Range
Set Ws = ActiveSheet Set Rng = Range(Cells(2, 7), Cells(Rows.Count, 7).End(xlUp))
For Each Cel In Rng Ws.PivotTables("PivotTable1").PivotFields("Point Name").CurrentPage = Cel & " "
Ws.Columns("A:B").Copy Sheets.Add With ActiveSheet .Paste .Name = Trim(Cel) .Range("A1").Select End With Next Ws.Activate End Sub
I turned it off for a while and when I turned it back on I am getting an error Unable to set the _Default property of the PivotItem class
Debugger is highlighting
Ws.PivotTables("PivotTable1").PivotFields("Point Name").CurrentPage = Cel & " "
I am trying to create a unique sample code by putting together the values of other cells that a user will input. It's all working well apart from the last part, where I am trying to include a decimal number. I want the decimal number to appear without the central "." and in a four digit format. e.g. 2.5 would appear as 0250, 14.25 would appear as 1425. This is the formlua I am using currently:
However, where the value of H4 is 2.5, I am getting a result of 0303 (I've put this part in bold). I have attached a small spreadsheet to aid understanding.
Pivot Chart. I would like to set up something to where a user can click on an individual value on a pivot chart (currently a line chart set up with 4 data series) and somehow display some underlying data. I have a lot of information stored in a data worksheet that I can't display all at once, but if a user sees a questionable data point, he/she can click and learn more about it from source data, or even a new query of the data worksheet.
I want to use a Pivot table to filter data to show just the studies that contain patients from the 'South' area?
As per example below I want to be able to see all the patients in all areas but only for studies that have patients from the south. I put together an array formula that works well for small tables but is too much with one one my sheets that contains 200,000 rows.
In the attached document is a timeline made from a scatter chart. Error bars using custom values are used to show the length of each task, however I can't get the chart to include error bars for the last 2 data points (tasks).
I have a file with a small example of sales and % of sales for about 7 persons.
I wanted to insert a pie chart but want to show labels inside the pie chart along with % of sales and the sales person name near each part. How to do this ?
