asked on

How to return the fill format of a an Excel chart area object in PowerPoint using VBA?

Im writing some VBA code to check the fill format of various elements of a chart object. If I create a new chart in a new presentation as follows I get some odd results:

1. Open PowerPoint
2. Create a new file from the default Office template
3. Insert a column chart
4. Select the resulting chart

You should have something like this:

Now, in the VBE Immediate window, execute this statement:

?ActiveWindow.Selection.ShapeRange(1).Chart.ChartArea.Format.Fill.Type

Open in new window

This returns msoFillMixed. In the PowerPoint UI, the fill is stated as "Automatic". Is this what msoFillMixed is being used for when a single object is selected?

Next, run this line:

?ActiveWindow.Selection.ShapeRange(1).Chart.ChartArea.Format.Fill.Visible

Open in new window

It returns True, which is where things get really weird as running the following returns black while what is on the slide is white! And neither black or white is selected in the UI colour picker.

?ActiveWindow.Selection.ShapeRange(1).Chart.ChartArea.Format.Fill.ForeColor.RGB

Open in new window

And wierdness continues if I try to force the fill to solid as this line changes the fill to Accent1:

ActiveWindow.Selection.ShapeRange(1).Chart.ChartArea.Format.Fill.Solid

Open in new window

So my question is how can I correctly detect the rendered fill state of the chart area?

ASKER CERTIFIED SOLUTION

John Korchok

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jamie Garroch (MVP)

ASKER

I've done some more digging and the reason black isn't highlighted in the colour picker UI is that the Fill.ForeColor.Type is set to RGB (msoColorTypeRGB) as opposed to the theme (msoColorTypeScheme). I also looked at the BackColor object and see a similar yet different set of properties e.g. it's set to white in this condition.

So I think this is a relatively save way to proceed:

Public Function IsFillFormatCorrectable(oFF As FillFormat) As Boolean
With oFF
If Not .visible Then Exit Function
Select Case .Type
Case msoFillMixed
If .ForeColor.Type = msoColorTypeMixed And _
.ForeColor.RGB = vbBlack And _
.BackColor.Type = msoColorTypeRGB And _
.BackColor.RGB = vbWhite Then IsFillFormatCorrectable = False Else IsFillFormatCorrectable = True
Case Else: IsFillFormatCorrectable = True
End Select
End With
End Function

John Korchok

That should do it.