Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Using VBA to format a single data label in excel 2010 bar chart

Posted on 2014-01-16
5
2,988 Views
Last Modified: 2014-01-23
I am trying to change the formatting of a single data label in a bar chart in Excel 2010.  I have a very short procedure for this using VBA, however it seems to be causing an odd problem.  After executing the code, it's as if the link between the label and the data has been broken, because even if the source data changes, the label does not.  The bar itself still seems to be linked, because it changes to reflect the new data, but the label does not.  Furthermore, the actual format I'm trying to apply does not do what it's supposed to either.

The only way to return to the correct value in the label is to delete labels for that data series and then apply them again.  I'm attaching an example if you'd like to check it out.  I reset the data labels in this version, so that you can run the applicable procedure and see if you're getting the same problem.  I appreciate your help.
Chart-Example.xlsx
0
Comment
Question by:Rich5150
  • 3
  • 2
5 Comments
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 39787702
Hi,

Could you post your code?

EDIT

if you change the formatting like here

ActiveChart.SeriesCollection(1).Points(4).DataLabel.NumberFormat = "0.00"

Open in new window


it shouldn't delete the link between data and the chart. But if you change the text in the label it is another story.

Regards
0
 

Author Comment

by:Rich5150
ID: 39788535
I'm sorry - put the code in the module and and then didn't save it as a .xlsm file, so the code didn't save.  I've attached another file with the code.  I'm not an excel rookie but apparently sometimes I act like it.

I played around with it some more, and the code I have to reformat the number is basically the same as what you have posted.  However, I was also changing the font size of the label, so I commented that out and ran it again and voila, it worked like it was supposed to.  I can probably get away with not changing the font size, but I don't understand why changing the font size on one label would break the link to the source data.  I have run the same code to change all of the labels in a data series at the same time, and it works just fine.

I already know that changing the text itself in the label will break the link, so I'm definitely not doing that.  If you have any ideas on what's going on with the code to change the font size I'd love to hear it.
Chart-Example.xlsm
0
 
LVL 50

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 39788839
Hi,

You do not need VBA to Format your Labels

you can use this format for the labels
[>=100]0,0.0

Open in new window


for the rest try this code, you have to set AutoText again to True to be able to capture the changes in the data

'Option Explicit
Sub AdjustLabels()
    Dim w As Integer
    Dim p As Integer
    Dim x As Integer
    'check if time for any process is equal to or greater than 100; if so, reformat data label
        w = 3 'row number
        p = 1 'process number
        x = 1
        
        Sheets("chart").Activate
        ActiveSheet.ChartObjects("Chart1").Activate
        For w = 3 To 10
            If Sheets("data").Cells(w, 2) >= 100 Then
                'ActiveChart.SeriesCollection(x).DataLabels.Select
                Set s = ActiveChart.SeriesCollection(x).Points(p).DataLabel
                With ActiveChart.SeriesCollection(x)
                    .Points(p).DataLabel
                    .Format.TextFrame2.TextRange.Font.Size = 8
                    .DataLabels.AutoText = True
                End With
            End If
            p = p + 1
        Next w
End Sub

Open in new window

Regards
Chart-Example1.xlsm
0
 

Author Comment

by:Rich5150
ID: 39788963
Thanks - hard to find guidance on this custom formatting stuff.

How can I use [>=100]0,0.0 but also format for zero values to not show decimal places?  My original formatting was #,##0.0;-#,##0.0;0;

What you suggested is a little different and I can't seem to make it work.
0
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 39790412
HI

Sorry. I assumed you would have only positive numbers. You will have to go the code route, because of the 4 expressions limit

for reference

http://www.databison.com/custom-format-in-excel-how-to-format-numbers-and-text/

Regards
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question