Solved

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

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

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 49

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 49

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
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 …

778 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