Solved: VBA to group few datalabel formatting options into a macro

[kevvukeka]

Hi All,

I have few charts on ppt slides. In each slide i need to select particular chart and select its data labels for format few of them as. border style = dash and border color = Green(R=0,G=128,B=0), underline and Bold. I need to do this for each chart and selected few data labels in it. Can i group the above formatting work in to a macro. so that I just need to select the data label and click the macro which will do the required formatting at single go. it would save me few hours as I report has around 400 slides.

I am an beginner to VBA.

Can anyone kindly help,

Thanks a lot!!

Regards,
Praveen

[John Wilson]

You really need to say which version of Office.
For 2010 this might work (not tested)

Sub DLFormat()
Dim ocht As Chart
Dim i As Integer
Set ocht = ActiveWindow.Selection.ShapeRange(1).Chart
For i = 1 To ocht.SeriesCollection.Count
If ocht.SeriesCollection(i).HasDataLabels Then
With ocht.SeriesCollection(i).DataLabels.Format.TextFrame2.TextRange.Font
.Fill.Visible = msoTrue
.Bold = True
.UnderlineStyle = msoUnderlineSingleLine
End With
With ocht.SeriesCollection(i).DataLabels.Format.Line
.Visible = True
.Weight = 1
.DashStyle = msoLineDash
.ForeColor.RGB = RGB(0, 128, 0)
End With
End If
Next i
End Sub

[kevvukeka]

Hi John,

Thanks for the help it really works. But I need a little tweek
This code currently selects all the data labels in the chart and applies a border green dotted line to it. but i need to apply for selected data labels only.

I need to select a particular datalabel and apply this formatting. so can we modify this a little for applyying to only the selected data label.

Thanks so much..

Regards,
Praveen

[John Wilson]

Not simple to do that but this might work to choose the series:

Sub DLFormat()
Dim ocht As Chart
Dim i As Integer
Set ocht = ActiveWindow.Selection.ShapeRange(1).Chart
i = InputBox("Which series?", "Select series to modify")
If ocht.SeriesCollection(i).HasDataLabels Then
With ocht.SeriesCollection(i).DataLabels.Format.TextFrame2.TextRange.Font
.Fill.Visible = msoTrue
.Bold = True
.UnderlineStyle = msoUnderlineSingleLine
End With
With ocht.SeriesCollection(i).DataLabels.Format.Line
.Visible = True
.Weight = 1
.DashStyle = msoLineDash
.ForeColor.RGB = RGB(0, 128, 0)
End With
End If
End Sub

[kevvukeka]

Hi John,

The above ask code for no.of series but then returns an error. Attached is dummy report with a single slide. I need to format the data labels as shown there.


the number of series varies from 20 to 30 some charts. It may not be easy to remember the exact number of series to format.

So is there way to select a partcular data label and then click on the macro button. 'coz i update these dotted boxes using another excel sheet.(by Alt+Tab). so is there a way to modify this code so that it runs based the data label that is selected inside the chart. one data label at a time.The one that is selected.

I know i am asking too much, but this is one of daily tasks..

Please see the attachement...

Thanks for your help...

Regards,
Praveen.

[John Wilson]

I don't kmow any way to work with the selected labels.

The chart you show has only one series and 14 points (numbered 1 - 14 starting at the bottom)

Maybe this would help NOTE The website has inserted a space in Fo nt - you will need to remove it.

Sub DLFormat2()
Dim ocht As Chart
Dim i As Integer
Set ocht = ActiveWindow.Selection.ShapeRange(1).Chart
i = InputBox("Which Point?", "Select point to modify")
If ocht.SeriesCollection(1).HasDataLabels Then

With ocht.SeriesCollection(1).Points(i).DataLabel.Format.TextFrame2.TextRange.Fo nt
.Fill.Visible = msoTrue
.Bold = True
.UnderlineStyle = msoUnderlineSingleLine
End With
With ocht.SeriesCollection(1).Points(i).DataLabel.Format.Line
.Visible = True
.Weight = 1
.DashStyle = msoLineDash
.ForeColor.RGB = RGB(0, 128, 0)
End With
End If
End Sub

[kevvukeka]

Thanks so much John!!!!!

This will reduce much of work... Thanks a ton!!!1

[kevvukeka]

Hi John,

I made a little change to your code as below.

Sub Green()
Dim ocht As Chart
Dim i As Integer
Dim j As Integer
Set ocht = ActiveWindow.Selection.ShapeRange(1).Chart
j = InputBox("Which bars?", "Select bars set to modify")
i = InputBox("Which Point?", "Select point to modify")
If ocht.SeriesCollection(j).HasDataLabels Then

With ocht.SeriesCollection(j).Points(i).DataLabel.Format.Line
.Visible = True
.Weight = 1
.DashStyle = msoLineDash
.ForeColor.RGB = RGB(0, 128, 0)
End With
End If
End Sub

i assigned this to a green button on toolbar. now everytime i run it. i need to run it 2 times for the output to come. the dotted line border doesnt appears in the first instance.

Do you know why its showing on the second run but not on the 1st run. i am working on ppt2010.

Thanks & Regards,
Praveen

[John Wilson]

Looks like the visible has to come AFTER you set the border

Sub Green()
Dim ocht As Chart
Dim i As Integer
Dim j As Integer
Dim x As Integer
On Error GoTo err
Set ocht = ActiveWindow.Selection.ShapeRange(1).Chart
Do
j = InputBox("Which bars?", "Select bars set to modify")
If j > ocht.SeriesCollection.Count Then
MsgBox "Out of range"
End If
Loop While j > ocht.SeriesCollection.Count
Do
i = InputBox("Which Point?", "Select point to modify")
If i > ocht.SeriesCollection(j).Points.Count Then
MsgBox "Out of range"
End If
Loop While i > ocht.SeriesCollection(j).Points.Count
If ocht.SeriesCollection(j).HasDataLabels Then
With ocht.SeriesCollection(j).Points(i).DataLabel.Format.Line
.Weight = 1
.DashStyle = msoLineDash
.ForeColor.RGB = RGB(0, 128, 0)
.Visible = True
DoEvents
End With
End If
Exit Sub
err:
MsgBox "You must select a chart", vbCritical
End Sub

[kevvukeka]

Hi John,

The code is inserting a black dotted border. Though its working in the first instance.

The RGB code is fine, but i am not able to fix why the color is black instead of green???

Thanks,
Praveen

[John Wilson]

Chart vba has always been a nightmare!

Maybe move the color line up.

Sub Green()
Dim ocht As Chart
Dim i As Integer
Dim j As Integer
Dim x As Integer
On Error GoTo err
Set ocht = ActiveWindow.Selection.ShapeRange(1).Chart
Do
j = InputBox("Which bars?", "Select bars set to modify")
If j > ocht.SeriesCollection.Count Then
MsgBox "Out of range"
End If
Loop While j > ocht.SeriesCollection.Count
Do
i = InputBox("Which Point?", "Select point to modify")
If i > ocht.SeriesCollection(j).Points.Count Then
MsgBox "Out of range"
End If
Loop While i > ocht.SeriesCollection(j).Points.Count
If ocht.SeriesCollection(j).HasDataLabels Then

With ocht.SeriesCollection(j).Points(i).DataLabel.Format.Line
.Visible = True
.ForeColor.RGB = RGB(0, 128, 0)
.Weight = 1
.DashStyle = msoLineDash
DoEvents
End With

End If
Exit Sub
err:
MsgBox "You must select a chart", vbCritical
End Sub

[kevvukeka]

Hi John,

The code is working fine.. Thanks so much again... Thanks a lot!!

Do you have any tutorials to learn more about chart VBA... Please ignore my request if i am not supposed to ask these in the forum....

Regards,
Praveen

[John Wilson]

I don't have much. As I said chart vba in PPT is a nightmare and varies from version to version.

I usually start by recording a macro in Excel to give me a clue but this rarely produces working code straight away!

[kevvukeka]

Ok, thanks John!!!

[John Wilson]

Ok, thanks John!!!

Thought you would like the file I worked with!
When you select a chart and Chart Tools > Format there should be a new button to run the code.

http://www.pptalchemy.co.uk/Downloads/Green.pptm

If you want it ALWAYS available save as choose ppam as the type and then go to Developer tab > AddIns and Add New.

If you cannot see the Developer Tab >> Options > Customize Ribbon and tick DEVELOPER

[kevvukeka]

Thanks John.. Will use it.. Actually I need to know all the options that can be done for a seriescollection-points. basically most of my work includes formatting the data labels in a chart or adding text boxes besides the data labels in a chart putting some alphabest in thos etext box..

What is the best to know all the options available for a data label. do i need to record them first in excel to know all the formatting options???

[mckeown55]

I have learned so much from reading this post alone - great

[kevvukeka]

Hi John,

Below is the code which you helped me few days back. In this code can we use multiple values for the input box. Using the below code I need to run it for each label which I need to format. which is good.

When I have a chart, where I know point 4, 5, 6 needs to have dotted green line I need to run this 3 times. So how can I use a input to give multiple values. For e.g 4,5,6 data labels need to be formattted with green dottled line.

Sub Green()
Dim ocht As Chart
Dim i As Integer
Dim j As Integer
Dim barc As Integer
Dim pointc As Integer
Set ocht = ActiveWindow.Selection.ShapeRange(1).Chart
barc = ocht.SeriesCollection.Count
pointc = ocht.SeriesCollection(1).Points.Count
j = InputBox("Which bars?", "Select bars set to modify-" & barc)
i = InputBox("Which Point?", "Select point to modify-" & pointc)
If ocht.SeriesCollection(j).HasDataLabels Then

With ocht.SeriesCollection(j).Points(i).DataLabel.Format.Line
.Visible = True
.Weight = 1
.DashStyle = msoLineDash
.ForeColor.RGB = RGB(0, 128, 0)
End With
End If
End Sub


Thanks Lot for your help.

Regards,
Praveen

[John Wilson]

This sort of thing maybe

Sub Green()
Dim ocht As Chart
Dim i As String
Dim x As Integer
Dim rayPoints() As String
On Error GoTo err
Set ocht = ActiveWindow.Selection.ShapeRange(1).Chart

i = InputBox("Which Points?", "Select points to modify, separated by comma e.g. 2,3,4")

If ocht.SeriesCollection(1).HasDataLabels Then
rayPoints = Split(i, ",")
For x = 0 To UBound(rayPoints)
With ocht.SeriesCollection(1).points(rayPoints(x)).DataLabel.Format.Line
.Visible = True
.ForeColor.RGB = RGB(0, 128, 0)
.Weight = 1
.DashStyle = msoLineDash
DoEvents
End With
Next x
End If
Exit Sub
err:
MsgBox "You must select a chart", vbCritical
End Sub

[kevvukeka]

Thank you John!!!