Solved

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

Posted on 2014-01-16
5
3,020 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
[X]
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
  • 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

756 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