Solved

Reformat Data Labels using VBA

Posted on 2011-09-17
2
532 Views
Last Modified: 2012-05-12
Dear Experts:

the attached sample file shows bar charts where ...
... the DATA LABELS contain the category name as well as the values separated by a semicolon. Position is 'Outside End'

Is it possible to manipulate the data labels of the charts in the following way, using VBA:
All data labels where ...
.. the category name is 1 and 2 have to be positioned inside ('Inside End') and ...
... the font color has to be changed to white. The other ones have to be left untouched.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas

 DataLabels-Reformat.xlsm
0
Comment
Question by:AndreasHermle
2 Comments
 
LVL 14

Accepted Solution

by:
pteranodon72 earned 500 total points
Comment Utility
AndreasHermle,

It looks like this should do it:

Dim c As Excel.Chart
Dim dl As Excel.DataLabel
Set c = {reference to the chart to update}
Set dl = c.SeriesCollection(1).Points(1).DataLabel
dl.Position = xlLabelPositionInsideEnd
dl.Format.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = vbWhite

Set dl = c.SeriesCollection(1).Points(2).DataLabel
dl.Position = xlLabelPositionInsideEnd
dl.Format.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = vbWhite

Set dl = Nothing
Set c = Nothing

Open in new window

HTH,
pT72
0
 

Author Comment

by:AndreasHermle
Comment Utility
Hi pT72:

great job, exactly like I wanted it.

I slightly tweaked the code since to be able to apply it to all the charts of the active worksheet (see below).

Thank you very much for your professional help.

Regards, Andreas
Sub Reformat_Data_Labels()

Dim dl As Excel.DataLabel
Dim myChtObj As ChartObject

For Each myChtObj In ActiveSheet.ChartObjects
        Set dl = myChtObj.Chart.SeriesCollection(1).Points(1).DataLabel
        dl.Position = xlLabelPositionInsideEnd
        dl.Format.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = vbWhite
        Set dl = myChtObj.Chart.SeriesCollection(1).Points(2).DataLabel
        dl.Position = xlLabelPositionInsideEnd
        dl.Format.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = vbWhite
Next

End Sub

Open in new window

0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
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…

728 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

12 Experts available now in Live!

Get 1:1 Help Now