MS-Graph DataLabels

[mcalex]

I'm a bit stuck on how to change the DataLabel type for MS-Graph
charts. There is a constant called xlDataLabelsType somewhere but
there doesn't seem to be any F1 help for it on either my work or home
versions of office2k. I want to set this (the data label type) to
show the label and percent (xlDataLabelsShowLabelAndPercent), but
can't put this in a DataLabel object whether this is a DataLabel that
hangs off a point, trendline, or seriescollection. I'm hoping that
anyone that's done vba in graph knows what I'm talking about. Any
sample code for manipulating datalabels very gratefully appreciated.

cheers

alex

 

[Tushar Mehta]

The following works fine in PP2000 and PP2003:

Sub Macro1()
'Dim x As Graph.Chart, y As Graph.Series, z As Graph.DataLabels
Dim x As Object, y As Object, z As Object
Const xlDataLabelsShowLabelAndPercent As Long = 5
Set x = ActiveWindow.Selection.ShapeRange.OLEFormat.Object
Set y = x.SeriesCollection(1)
y.DataLabels.Delete
y.ApplyDataLabels xlDataLabelsShowLabelAndPercent, Separator:
=vbNewLine
'y.ApplyDataLabels xlDataLabelsShowValue, Separator:=vbNewLine
Set z = y.DataLabels
z.AutoScaleFont = False
z.Font.Size = 9
End Sub

Do note that you have to select the MS Graph object before running the
sample code.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[mcalex]

Thank you Tushar. This works nickely.

PS: Is there a way of activating a particular slide, or returning the
active slide (rather than having to select a chart) prior to running
code on it. My aim is to programmatically change approximately 7
slides containing customer specific information in a presentation of
about 30 slides. This will allow customers to d/load the
presentation, and when they open it, all the graphs (etc) will be
tailored to their situation. I'd really like something along the
lines of:
slideNo = ActivePresentation.ActiveSlide.Index
or ActivePresentation.Slides(7).Activate
but I can't see commands like these in the doco. Any suggestions?

Thx again
Alex

 

[Tushar Mehta]

You are welcome.

Finding the 'current' slide is something I've always had to stumble
through. For some reason, that part of the PP object model doesn't
quite work for me. Of course, I always find a way, just that it
involves some fumbling and some stumbling...

Try some of the following:

The type of view: activewindow.ViewType

The current slide (or the first slide in a multi-slide selection):
activewindow.View.Slide

The ID of the current slide:
activewindow.View.Slide.SlideID

To see how many slides are selected:
activewindow.Selection.SlideRange.Count

To find the current slide in a slideshow:
activepresentation.SlideShowWindow.View.CurrentShowPosition

A specific shape in a specific slide:
activewindow.presentation.Slides(1).Shapes(3)
or
activepresentation.Slides(1).Shapes(3)

To check the type of a shape:
activepresentation.Slides(1).Shapes(3).Type

If it is an embedded OLEObject the type will be msoEmbeddedOLEObject

To access the embedded OLE object:
activewindow.presentation.Slides(1).Shapes(3).oleformat.Object

To check if the OLEObject is a MSGraph chart:
typename(activewindow.presentation.Slides(1).Shapes
(3).oleformat.Object) should be the string "Chart"

So, if you know that slide 1 shape 3 is the MSGraph object, a version
that does not require you to select the object (or even the slide)
before running the macro:

Sub Macro1_NoSelect()
Dim x As Object, y As Object, z As Object
Const xlDataLabelsShowLabelAndPercent As Long = 5
Set x = ActivePresentation.Slides(1).Shapes(3).OLEFormat.Object
Set y = x.SeriesCollection(1)
y.DataLabels.Delete
y.ApplyDataLabels xlDataLabelsShowLabelAndPercent, _
Separator:=vbNewLine
Set z = y.DataLabels
z.AutoScaleFont = False
z.Font.Size = 9
End Sub

And, finally, a version that checks every shape in every slide:

Sub Macro1_AllSlides_AllShapes()
Dim x As Object, y As Object, z As Object
Dim aSlide As Slide, aShape As Shape
Const xlDataLabelsShowLabelAndPercent As Long = 5
For Each aSlide In ActivePresentation.Slides
For Each aShape In aSlide.Shapes
If aShape.Type = msoEmbeddedOLEObject Then
If TypeName(aShape.OLEFormat.Object) = "Chart" Then
Set x = aShape.OLEFormat.Object
Set y = x.SeriesCollection(1)
y.DataLabels.Delete
y.ApplyDataLabels xlDataLabelsShowLabelAndPercent, _
Separator:=vbNewLine
With y.DataLabels
.AutoScaleFont = False
.Font.Size = 9
End With
End If
End If
Next aShape
Next aSlide
End Sub


--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

Thank you Tushar. This works nickely.

PS: Is there a way of activating a particular slide, or returning the
active slide (rather than having to select a chart) prior to running
code on it. My aim is to programmatically change approximately 7
slides containing customer specific information in a presentation of
about 30 slides. This will allow customers to d/load the
presentation, and when they open it, all the graphs (etc) will be
tailored to their situation. I'd really like something along the
lines of:
slideNo = ActivePresentation.ActiveSlide.Index
or ActivePresentation.Slides(7).Activate
but I can't see commands like these in the doco. Any suggestions?

Thx again
Alex