Free Microsoft Excel 2013 Quick Reference

Format specific chart data labels using VBA

Hi all! I am designing a database for work to create a scatter plot chart based on supplier ratings from 1-10. I recently found coding with VBA that labels each point with the names of the suppliers on the chart at the address: http://excel.tips.net/Pages/T003503_...ter_Plots.html

However, problem one now is that I don't know how to manipulate the code and need to change the formatting (font, size, ect)

Also, problem two I have is that I only want some of the points labeled and the others to just be points not labeled zero.

My excel worksheet is set up with:
column A as the company names
B: x-axis points
C: y-axis points

I need two parts to the formatting of the chart:

1. A gradation of green to yellow to red fading from the bottom left corner to the top right
2. Curved lines connecting the 3,5,7,10 on the x-axis to the 3,5,7,10 on the y-axis.
3. Then of course the text labeling of only some data points.

The code that I found is:

Sub DataLabelsFromRange() Dim Cht As Chart Dim i, ptcnt As Integer Set Cht = ActiveSheet.ChartObjects(1).Chart On Error Resume Next Cht.SeriesCollection(1).ApplyDataLabels _ Type:=xlDataLabelsShowValue, _ AutoText:=True, _ LegendKey:=False ptcnt = Cht.SeriesCollection(1).Points.Count For i = 1 To ptcnt Cht.SeriesCollection(1).Points(i).DataLabel.Text = _ ActiveSheet.Cells(i + 1, 1).Value Next iEnd Sub

If anyone can point me in the right direction I would really apreciate it! I am lost with programing Thanks so so much for your help!!!


Post your answer or comment

comments powered by Disqus
Hi,
I need to get the value of a chart data point using VBA. I tried the ActiveChart.SeriesCollection(x).Points(x).properties but cannot return any information.

Alternatively returning the source range of the chart will be enough as I could get the data straight from the spreadsheet, but I cannot find a way of returning/getting the source range of a chart.

Please help I cannot find a similar problem with a solution so far.

Thanks
Ray

Hi,

Using Excel 2003; I have a pie chart whose chosen Chart Options/Data Labels
are "Category Name" and "Percentage" which in turn are linked to a
concatenated formula showing manager name, dollar amounts and then
percentages.

When these linked formula values are updated, the chart data labels do not
automatically update and require me to go to Chart Options/Data Labels and
deselect then reselect "Category Name" and "Percentage" to force them to
update and then I have to reformat them, etc, etc.

Am I doing something wrong or does excel really not update these values on
the fly? My calculation is set to automatic.

Please let me know if it is possible to have them automatically update.

Thank you.
Orlando

--
Orlando Vazquez

Hi, I have chart data labels that are displaying as one line in Excel but
push over into two lines when paste/linked into a PowerPoint slide. Is there
some way to force the labels to stay on one line? And as a general matter,
is it possible to manipulate the size of the box containing the data label at
all? I can select it but can't seem to resize manually. I also looked for
info on this in VBA help but couldn't find anything.

Any help would be appreciated.

Kind regards,
Don

When I make changes to the data that are the data source for a pie chart, the pie chart data labels always fail to update. I would expect this from Excel97, but I'm working in Excel 2003... Is this autoupdating issue still a known Excel bug? Is there a straightforward workaround?

Thanks!

- pk
-----------------------------------------------------------------------------------------
"The odds are good, but the goods are odd."

Is there a way to add more information to a pie chart data label other than
manually edit, as this disables the link to source aspect.

Hi All,

This the second time I am posting this thread and am hoping that this time someone would reply.

I have a simple line chart for following data

Week Ending Sales
27/04/08 £1,234.00
04/05/08 £1,542.50
11/05/08 £2,776.50
18/05/08 £4,319.00
25/05/08 £5,861.50
01/06/08
08/06/08

The line has data Labels showing the values(With £ sign). But everytime I add a new data in 'Sales' column and extend the range of the line, the data label formatting changes and they show the values with $ sign. (for e.g. if I enter £6000.00 for week ending 01/06/08 and exten the range by dragging it to include that cell as well, all the data labels show values with $ sign)
I do not have a clue why this is happening. It would be great if someone can suggest me how to tackle this problem.

Thanks in Advance,

ManUtd Auto Merged Post Until 24 Hrs Passes;

Don't whether this will help...But I am using Excel 2007

Hi All,

I'm running Excel 2K on Win 2K

I've created a Column Chart off of a pivot table. I chose "Show Values" on
the Data Labels tab of Format Data Series. The labels show up. On the
"Alignment" tab of Format Data Labels there is a "Label Position" drop down.
This drop down consists of "Inside End, Center, Inside Base", three choices.
None of these are what I want.

I created a new chart using the exact same procedure as above. However it
had a fourth option under "Label Position" called "Outside End". This is
exactly what I want.

My question is, and I feel stupid, is I can't get that "Outside End" option
to come up on my next chart.

Does anyone know what I might be doing wrong???

Thanks in advance.

Hi All,

I'm running Excel 2K on Win 2K

I've created a Column Chart off of a pivot table. I chose "Show Values" on
the Data Labels tab of Format Data Series. The labels show up. On the
"Alignment" tab of Format Data Labels there is a "Label Position" drop down.
This drop down consists of "Inside End, Center, Inside Base", three choices.
None of these are what I want.

I created a new chart using the exact same procedure as above. However it
had a fourth option under "Label Position" called "Outside End". This is
exactly what I want.

My question is, and I feel stupid, is I can't get that "Outside End" option
to come up on my next chart.

Does anyone know what I might be doing wrong???

Thanks in advance.

I have a large workbook with many charts. These charts all have the same scale but I need to have the capability to change the scale on these charts across the entire workbook. Coding this was not difficult and works correctly on charts that do not have data labels.

On charts with data labels the chart scales but the data labels are no longer displayed (see screenshots). The level that the bar chart portion of the chart was initially at is shifted lower when the scale is modified as well, this can also be seen in the screenshots.

I think there must be a property that I am missing. Something that will allow me to force the data labels back to their initial settings or maybe it is a property of the chart itself, I just cannot find what I need. Perhaps the scaling could be done differently as well.

Any help would be greatly appreciated!

This is the code used to scale the charts:

With chtobj.Chart.Axes(xlValue)
.MinimumScale = minScale
.MaximumScale = maxScale
.MajorUnitIsAuto = False
.MajorUnit = majUnit
End With
With chtobj.Chart.Axes(xlValue, xlSecondary)
.MinimumScale = minScaleSecondary
.MaximumScale = maxScaleSecondary
.MajorUnitIsAuto = False
.MajorUnit = majUnitSecondary
End With

I have a dashboard with a series of line charts.
The client's required style is that there be data labels only on the rightmost point of the line series, containing the series name and most recent value.
The dashboard is run for 19 different categories, and everything else automatically updates nicely, except these labels, which as the data changes, look terrible. I am currently manually moving labels around or modifying axis parameters on multiple charts x 19.
A sample chart illustrating the problem is attached.

Can anybody think of how I can have excel automatically format the chart so that the labels do not overlap? I suspect it might have to be VBA, but I can't even think of what that VBA would need to do.

I have a macro that fixes the data labels since my client doesnt want them on the position excel has like center,inside end. etc. My client wants the label on top of the bars so basically this is the code in my macro


	VB:
	
 
ActiveSheet.ChartObjects("Chart 1").Activate 
ActiveChart.SeriesCollection(2).DataLabels.Select 
With Selection 
    .Position = xlLabelPositionInsideEnd 
End With 
ActiveSheet.ChartObjects("Chart 1").Activate 
For i = 1 To ActiveChart.SeriesCollection(2).Points.Count 
    ActiveChart.SeriesCollection(2).DataLabels.Select 
    ActiveChart.SeriesCollection(2).Points(i).DataLabel.Select 
    Selection.top = Selection.top - 17 
Next i 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
the macro does it well and fixes the data labels. My problem is once I save it and re open the file the labels would move to the original position which is xlLabelPositionInsideEnd. This worked fine for me in excel 2003 now I'm stumped in 2007

Additional info:
This seems to be the problem when saving the file in .xls I tried this with just dummy datas and a new workbook and it kept happening to me when im in saving it in .xls but if I save it in .xlsx the positioning would stay put. The problem is that I'm not allowed to use .xlsx format since some clients are still using 03 is there a work around for this.

Hi there. Please can anyone help me with this problem. I have a scatter chart and have applied data labels using the VBA macro supplied with Excel 2007. They pick up the cells in column A. But I now want to use Autofilter to show different ranges in the chart. Everytime I filter the chart data, the data labels change to show a different label (in fact they seem to be starting from the first label again, even if this value is not shown on the filter). I have tried to edit each data label and enter free text, I've also used a formula to link to the cell and made it absolute, but nothing is working.

The chart must be a scatter chart as it is plotting 2 values. I'm trying to create a Project Portfolio Risk Chart showing Benefits against Difficulty.

Can anyone help with this?

Thanks
JD

I have an existing Word document with three charts in it.
I have an Excel spreadsheet with a VBA Macro that does some calculations and assigns the results to variables.
I want to update the chart data in Word with the values of the variable in excel.
This code correctly locates the second and third charts but puts the same variables into the cells for both charts:

	VB:
	
 InlineShape 
For Each objShape In ActiveDocument.InlineShapes 
    If objShape.HasChart Then 
        objShape.Chart.ChartData.Activate 
        Range("b2").Value = variable4 
        Range("b3").Value = variable5 
    End If 
Next 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
First problem is, it completely skips over the first chart in the Word document so I assume the argument objShape.HasChart = False for some reason.
Second problem is, it assigns values for variable4 and variable5 to the second and third charts in the word document. I want to assign variable1,2,3 to the first chart, variable4 and variable5 to the second chart and variable6 and variable 7 to the third chart.

Another line of thinking for me was to forget about updating the existing charts and just insert new charts based on the various variable data. I can not seem to figure out how to place a chart at a very specific location in the Word document. I tried selecting a pre-positioned bookmark, then adding the chart but it just put the chart at the beginning of the Word document.

	VB:
	
WD.ActiveDocument.Bookmarks("Chart").Select 
Set RFactorChart = WD.ActiveDocument.Shapes.AddChart.Chart 
Set chartWorkSheet = RFactorChart.ChartData.Workbook.Worksheets(1) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
For the love of God, please help me. I'm already bald or I would be pulling my hair out!

Hi,

I am simply trying to enable data labels on a MSGraph.Chart using VBA.

Here is the code I am trying to use:

Code:
Dim oGraph As Graph.Chart
Dim chtSeries As Graph.Series

With oGraph

For Each chtSeries In .SeriesCollection
        
        'Enable Data Labels in the chart
        
        chtSeries.HasDataLabels = True
    
    Next

End With
However, I keep getting an error. Can anyone see why this is?

This question was posted about 3 years ago and the answer was to use a
third party tool. That seems a bit ridiculous when the functionality
seems to be inherently in Excel.

The problem: I have a stacked bar chart in Excel and would like to
show the percentages for each of the datasets in the bars. When I go
to Format > Format Data Series > Data Labels, I can select Series
Name, Category Name, and Value for "Label Contains" but Percentage and
Bubble Size are both unavailable (greyed out). I need the percentages.
How do I get those to display?

Using Excel 2003. In a pivot table line chart, if you use the option to
display values on the data set line in the chart, the values disappear when
you drill down into the data. So for example, if the data represents sales
for a number of locations, when you use the pull down to pick a specific
location, the data labels disappear. How can you maintain the option to
display labels with each drill down?

How do you position a data label on top of the data point in a radar chart?
In an XY scatter plot you can select this from within the Format Data Labels
window and then select Label Position: Center. In a Radar chart you are not
given this option. Why? How could you work around it using a Visual Basic
Macro?

Hi,

I have a line chart with a few lines of data (which is formatted as £ currency).

The axis on the chart is also formatted as £ currency, however when hovering over the data labels, it shows the amount in Dollars ($).

Does anyone know why this happens?

Thanks

James

After creating a chart, there is bug when you format the data labels. If you
go to "Format data labels..." then , on "numbers" click "custom", click
another option, then "custom" again, the itens shown on custom disappear,
showing only unsupported characters.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...heet.functions

I have a stacked column chart.
How do I format all of the data labels togethor (i.e. to set them all
to a smaller font?)

I can do one stack at a time, but not all of them. Also I have data for
future months that will be populated later, so its not feasible to only
format the visible data labels. Thanks!

I am trying to write an If statement that returns true if Valule is checked off in the Data Labels portion of a chart. I tried using a HasDataLabel properties but Excel didn't recognize that. Please help.

I am trying to create a 100% stacked column chart from an existing pivot table.

I have each column (MONTH) representing the % of the count of parts ORDERED within three PRIORITY catagories: Emergency, Rush and Rountine.

I would like the data labels for each point to show, not the count of parts ORDERED, but the sum of COST of each PRIORITY in each column (MONTH).

Is this possible using Excel 2000 or 97?

(All caps above indicate existing columns in the pivot table.)

Hello,

I am trying to create a conditional format using VBA and apply that format to several rows in 2 columns. When I walk through the code the formula string contains the correct cell reference, however when the macro completes and I review the conditional format for the cells in the sheet the cell reference starts 9 rows and 1 column past what I want. I want the formula in cell A10 to be "=LEN(A10)>0" but when its executed I get "=LEN(A19)>0"
Below is the code I've created. (Note: wrkSht is a worksheet variable initialized to the correct sheet. This code is in a method inside of a class module)


	VB:
	
firstRow = 10 
firstCol = 1 
lastRow = 500 
lastCol = 3 
Set rng = wrkSht.Range(wrkSht.Cells(firstRow, firstCol), wrkSht.Cells(lastRow, lastCol)) 
rng.FormatConditions.Delete 
 
strFormula = "=LEN(A10)>0" 
rng.FormatConditions.Add Type:=xlExpression, Formula1:=strFormula 
rng.FormatConditions(1).Interior.ColorIndex = 24 
 
Set rng = Nothing 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi guys,

Great looking forum, just found it today when trying to get an answer to this question...

How do you make a graph/chart in word that can be edited using VBA.

I would like a graph that a user can make changes to the data using vba textboxes, and maybe change the graph type using tickboxes (pie/line/etc)

-- One step at a time though if somone could just tell me how to actually just change the value of a "microsoft graph chart" datasheet, i will prob be able to work the rest out myself.

Thanks for any help.

Regards.


No luck finding an answer? You could always try Google.