Solved

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

Posted on 2014-01-16
5
2,880 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 48

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 48

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 48

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

758 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now