Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2014-01-16
5
Medium Priority
?
3,251 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 53

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 53

Accepted Solution

by:
Rgonzo1971 earned 2000 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 53

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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
If you are a mobile app developer and especially develop hybrid mobile apps then these 4 mistakes you must avoid for hybrid app development to be the more genuine app developer.
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…
Screencast - Getting to Know the Pipeline

916 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