?
Solved

How do I use VBA to add and format data labels

Posted on 2011-10-04
1
Medium Priority
?
260 Views
Last Modified: 2012-05-12
I have roughly 50 bars in an excel chart and below is some VBA that someone from experts exchange helped me to custom format the colors of the bars.  I would like to add to this and add data labels 8 pt aerial font with one decimal place.  Below is the code for one of the 50 points on the graph.  

I am happy to edit each one if necessary, but is it possible to create a macro that affects all the bars in terms of data labels.
Sub FormatChartPoints()
   Dim cht As Chart
   Dim n As Long
   Set cht = Charts("quintile chart")
   With cht.SeriesCollection(1)
      With .Points(1).Format
      With .Fill
         .Visible = msoTrue
         .ForeColor.RGB = RGB(0, 51, 204)
         .Transparency = 0
         .Solid
      End With
      With .Line
         .Visible = msoTrue
         .ForeColor.RGB = RGB(0, 0, 0)
         .ForeColor.TintAndShade = 0
    End With
    End With

Open in new window

0
Comment
Question by:majervis
1 Comment
 
LVL 42

Accepted Solution

by:
dlmille earned 1000 total points
ID: 36915051
Just a couple additional lines - ensure datalabels are on, then set the numberformat and font.size:

 
Sub FormatChartPoints()
Dim cht As Chart
Dim n As Long

    Set cht = Charts("quintile chart")
   
    With cht.SeriesCollection(1)
        With .Points(1).Format
            With .Fill
            .Visible = msoTrue
            .ForeColor.RGB = RGB(0, 51, 204)
            .Transparency = 0
            .Solid
            End With
            With .Line
            .Visible = msoTrue
            .ForeColor.RGB = RGB(0, 0, 0)
            .ForeColor.TintAndShade = 0
            End With
        End With
        .HasDataLabels = True
        .DataLabels.NumberFormat = "##.0"
        .DataLabels.Font.Size = 8
    End With
End Sub

Open in new window

0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

864 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